# App Development

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
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!

1 Solution

Accepted Solutions
Contributor III
Author

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

Expression

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

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

7 Replies
Partner - Specialist III

Hi Rishi,

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

+

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

Thanks,

Arvind Patil

Partner - Master III

Try,

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

or

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

Contributor III
Author

Hi Arvind

Contributor III
Author

Tried that but still does no luck!

Partner - Master III

can you share sample app or screenshot of data

Partner - Contributor III

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

Contributor III
Author

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

Expression

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

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

Tags
Community Browser