How to get values for max month in each year

[ruanhaese]

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

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;

[bbmmouha]

you shoud do the inverse of this

Max(aggr(),year)

[Gowtham174 · Accepted solution]

Try this

SUM({<FPeriod = {'<=$(=Max(FPeriod))'}>}IF(FPeriod = Aggr(NODISTINCT Max({1}FPeriod),FYear), Amount))

[ruanhaese]

Thanks.

I just changed your single quotes to double quotes but otherwise it works.