Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qvqfqlik
Creator
Creator

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
Champion
Champion

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');

qvqfqlik
Creator
Creator
Author

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

the max expression is not working yet.

YoussefBelloum
Champion
Champion

Make sure that:

MonthYear field + vStart +vEnd have exactly the same format

qvqfqlik
Creator
Creator
Author

they have same format

sunny_talwar

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

sunny_talwar

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))