topic Re: Dynamic 'previous month' Selctions in Set Analysis in App Development

Dynamic 'previous month' Selctions in Set Analysis

Anonymous — Fri, 03 Nov 2017 10:07:44 GMT

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!

Re: Dynamic 'previous month' Selctions in Set Analysis

arvind_patil — Fri, 03 Nov 2017 11:05:51 GMT

Hi Rishi,

Please try below:

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

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

Thanks,

Arvind Patil

Re: Dynamic 'previous month' Selctions in Set Analysis

shraddha_g — Fri, 03 Nov 2017 11:10:30 GMT

Try,

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

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

Re: Dynamic 'previous month' Selctions in Set Analysis

Anonymous — Fri, 03 Nov 2017 11:14:14 GMT

Hi Arvind

Yes iv tried that already but had no luck.

Re: Dynamic 'previous month' Selctions in Set Analysis

Anonymous — Fri, 03 Nov 2017 11:33:47 GMT

Hi Shraddha

Tried that but still does no luck!

Re: Dynamic 'previous month' Selctions in Set Analysis

shraddha_g — Sat, 04 Nov 2017 03:30:41 GMT

can you share sample app or screenshot of data

Re: Dynamic 'previous month' Selctions in Set Analysis

Simon_J — Thu, 23 Nov 2017 11:10:24 GMT

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

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

Re: Dynamic 'previous month' Selctions in Set Analysis

Anonymous — Thu, 23 Nov 2017 11:51:49 GMT

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