topic Re: Previous Months in QlikView

Previous Months

kevbrown — Wed, 03 Aug 2016 11:03:56 GMT

I've been asked to produce a report that shows current month, last month and the month previous to that. The way they'd like this to work is by having buttons for this month, last month and the month previous to that. I can get this month to work "select" Max(Month) but how do i get the previous 2 months? Max(Month)-1 doesn't work

Re: Previous Months

raghvendrasingh — Wed, 03 Aug 2016 11:12:35 GMT

Hi Kevin,

Can you please share your application, in which its not working. Because in text box i checked that it is working fine.

Please share the exact requirement what you exactly want?

Regards

Raghvendra

Re: Previous Months

rubenmarin — Wed, 03 Aug 2016 11:15:30 GMT

Hi Kevin, Max can have a parameter to get the 2nd Max value:

Max(Month, 2)

But when talking about something related to dates usually is better to use functions related to dates:

AddMonths(Max(Date), -2) // Will return a date two months earlier for the max date

If Max(Date) is '01/02/2016' it will return '01/12/2015'. So Month(AddMonths(Max(Date), -2)) will return December, wich is two months before the max possible date.

Re: Previous Months

Anonymous — Wed, 03 Aug 2016 11:18:12 GMT

you create a variable e.g. vSelectedMonth

button for this month you define Action Set Variable vSelectedMonth = max(Month)

button for last month you define Action Set Variable vSelectedMonth = date(addmonths(max(Month),-1))

button for month previous you define Action Set Variable vSelectedMonth = date(addmonths(max(Month),-2))

-> Month must be defined as datefield (not as text)

in your table etc. you create Expression: sum({<Month={'$(=vSelectedMonth)'}>}Value

Re: Previous Months

Anonymous — Wed, 03 Aug 2016 11:20:59 GMT

Date(Addmonths(max(Date),-1),'DD/MM/YYYY') for previous month

Date(Addmonths(max(Date),-2),'DD/MM/YYYY') for two months ago

But if you are doing sum aggregate functions like sum or count you need to work on set analysis

like this ....

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DateFieldFormatHere') & '<=' &Date(MonthEnd(Max(Date)), 'DateFieldFormatHere'))"} >}Sales)