Dynamic 'previous month' Selctions in Set Analysis

[Anonymous]

Hi

I have a KPI number where I want to basically show the current month amount and the previous month amount, based on the month a user selects. Current month works fine using:

sum({<Month={$(=MaxString(Month))} >}Amount)

I cannot get the previous month when simply including the '-1' or am I missing something obvious.

Any help would be great!

Thanks in advance!

[arvind_patil]

Hi Rishi,

Please try below:

= sum({<Month={$(=MaxString(Month))} >}Amount)

+

sum({<Month={$(=MaxString(Month),-1)} >}Amount)

Thanks,

Arvind Patil

[shraddha_g]

Try,

sum({<Month={"$(=max(Month)-1)"} >}Amount)

or

sum({<Month={"$(=num(Max(Month))-1)"} >}Amount)

[Anonymous]

Hi Arvind

Yes iv tried that already but had no luck.

[Anonymous]

Hi Shraddha

Tried that but still does no luck!

[shraddha_g]

can you share sample app or screenshot of data

[Simon_J]

Hi Rishi,

Is the month field numeric?

What happens when you then have more than one year of data? Would it not be better to use Month and year?

If i was building this, i would create a variable for vCurrentMonth and vPreviousMonth and then use the following set analysis

Sum({<Month={"$(vCurrentMonth)"}>}Amount)

Sum({<Month={"$(vPreviousMonth)"}>}Amount)

You can then set up a calc in each to work out the current and previous month.

Something like the below:

=month(addmonths(max(Month),-1))

or

=Year(addmonths(max(Month),-1))

[Anonymous · Accepted solution]

Hi Simon

After a few more attempts I managed to get it working similar to what you mentioned. The syntax for the set analysis in Qlik Sense using a variables is slightly different.

vCurrentMonth   = Date(max([MonthYear]), 'MMM-YY')

vPreviousMonth = Date(addmonths(max([MonthYear]), -1), 'MMM-YY')

Expression

sum({$<MonthYear ={'$(=$(vCurrentMonth))'}>}[Expense Amt])

sum({$<MonthYear = {'$(=$(vPreviousMonth))'}>}[Expense Amt])