7 Replies Latest reply: Nov 23, 2017 6:51 AM by Rishi Minawala

# Dynamic 'previous month' Selctions in Set Analysis

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!

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

Hi Rishi,

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

+

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

Thanks,

Arvind Patil

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

Hi Arvind

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

Try,

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

or

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

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

Tried that but still does no luck!

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

can you share sample app or screenshot of data

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

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:

or

• ###### Re: Dynamic 'previous month' Selctions in Set Analysis

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