Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

r15hnm777
New Contributor III

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!

Thanks in advance!

1 Solution

Accepted Solutions
r15hnm777
New Contributor III

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

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

Expression

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

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

7 Replies
arvind_patil
Valued Contributor II

Re: Dynamic 'previous month' Selctions in Set Analysis

Hi Rishi,

Please try below:

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

+

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



Thanks,

Arvind Patil


shraddha_g
Honored Contributor III

Re: Dynamic 'previous month' Selctions in Set Analysis

Try,

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

or

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

r15hnm777
New Contributor III

Re: Dynamic 'previous month' Selctions in Set Analysis

Hi Arvind

Yes iv tried that already but had no luck.

r15hnm777
New Contributor III

Re: Dynamic 'previous month' Selctions in Set Analysis

Hi Shraddha

Tried that but still does no luck!

shraddha_g
Honored Contributor III

Re: Dynamic 'previous month' Selctions in Set Analysis

can you share sample app or screenshot of data

simonj
New Contributor III

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:

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

or

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

r15hnm777
New Contributor III

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

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

Expression

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

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

Community Browser