3 Replies Latest reply: Oct 29, 2018 3:35 AM by Ruan Haese RSS

    How to get values for max month in each year

    Ruan Haese

      Hi all.

      Cant seem to get this right, think it will help me a bit with a better understanding

      of how it all works.

       

      I'm trying to get the Max Month in each year. So if you have a chart that shows

      the year 2016, 2017 and 2018 then it should return the amount for Dec 2016,  Dec 2017 & Sep 2018

       

      In the following Example, it should return the values for Sep 2016, Sep 2017, Sep 2018 only

      (i.e. not the sum of the whole year).

       

      The Calc1 Formulae is:

      SUM({< FPeriod={"$(=Aggr(Max(FPeriod),FYear))"}>} Amount )

       

      The AGGR returns the correct values in the top table, but on the wrong rows, returning zero.

      If I take the FPeriod column out (bottom table) then Max FPeriod and AGGR FPeriod returns the correct values

      but the Calc still doesnt return anything.

       

      Finally if I just use Max(FPeriod) instead of AGGR then it only returns 300 (which is correct as

      its showing the Sep 2018 value only).

       

      Expected Result

      Im looking to get the max period value for each year, so the result should be

      2016 -  900

      2017 -  600

      2018 -  300

       

      Thank you

       

      Results.PNG

       

       

       

      TmpData:

      LOAD * INLINE [

      Product,Country,Amount,CountryName,Period

      Shoes,FR,100,France,03/01/2018

      Shoes,DE,200,Germany,06/01/2018

      Dress,FR,300,France,09/01/2018

      Dress,DE,400,Germany,03/01/2017

      Shirt,FR,500,France,06/01/2017

      Shirt,DE,600,Germany,09/01/2017

      Shirt,FR,700,France,03/01/2016

      Shirt,DE,800,Germany,06/01/2016

      Shirt,FR,900,France,09/01/2016

      ];

       

      Data:

      NoConcatenate

      LOAD

      *

      ,Date(Date#(Period,'MM/DD/YYYY'),'DD-MMM-YYYY') AS FPeriod

      ,Year(Date#(Period,'MM/DD/YYYY')) AS FYear

      RESIDENT TmpData;