Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Partner
Partner

How to get values for max month in each year

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;





1 Solution

Accepted Solutions
Highlighted
Gowtham174
Contributor

Re: How to get values for max month in each year

Try this

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

View solution in original post

3 Replies
Highlighted
bbmmouha
Contributor

Re: How to get values for max month in each year

you shoud do the inverse of this

Max(aggr(),year)

Highlighted
Gowtham174
Contributor

Re: How to get values for max month in each year

Try this

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

View solution in original post

Highlighted
Partner
Partner

Re: How to get values for max month in each year

Thanks.

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