Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

priqvqlik
Contributor

max value

Please help. I am trying to calculate max value in the last 3months

but nothing is shown when I use this below expression

=max(Sum({<MonthYear={">=$(vStart)<=$(vEnd)"}>} sales ))

vStart=NUM(year(Today())) & NUM(month(Today()),'00')

vEnd= Date(Addmonths( Date(today(),'YYYYMM'),-3),'MMM-YYYY')

eg:

month                 sales

Dec-2017             100

Nov-2017             200

Oct-2017              50

Sept-2017            20

6 Replies
YoussefBelloum
Esteemed Contributor

Re: max value

it is not working because the format of your dates variables are different:

vStart= 201801

vEnd: 10-2017

if you have your Field monthfield with this format MMYYYY, i suggest you this:

let vStart=NUM(month(Today()),'00')&NUM(year(Today()));

let vEnd= Date(Addmonths( Date(today(),'YYYYMM'),-3),'MMYYYY');

priqvqlik
Contributor

Re: max value

I changed vStart=Date(today(),'MMM-YYYY')

the max expression is not working yet.

YoussefBelloum
Esteemed Contributor

Re: max value

Make sure that:

MonthYear field + vStart +vEnd have exactly the same format

priqvqlik
Contributor

Re: max value

they have same format

Re: max value

You cannot have Max() on top of Sum() without Aggr() function... should be like this

Max(Aggr(Sum({<MonthYear={">=$(vStart)<=$(vEnd)"}>} sales), MonthYear))

Where

vStart = Date(Addmonths(Today(), -3),'MMM-YYYY')

vEnd = Date(MonthStart(Today()), 'MMM-YYYY')

Where MonthYear is created like this in the script

Date(MonthStart(DateField), 'MMM-YYYY') as MonthYear

Re: max value

In a chart with MonthYear as dimension, if you want to see the Max() across all months, then try this

Max(TOTAL Aggr(Sum({<MonthYear={">=$(vStart)<=$(vEnd)"}>} sales), MonthYear))

Community Browser