Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
RRamos
Contributor II
Contributor II

Calculate previous month data based on selection

Hi all,

I am trying to work out how to display previous month's data based on the current month selection. 

If I select May then I want Apr data displayed.

So far I have tried using the AddMonths function within set analysis (example below) but nothing is being returned. 

=Sum({$ <date field={"$(=AddMonths(date field, -1)"}>} [Amount])

Any help will be much appreciated.

Thanks

 

4 Replies
sunny_talwar

May be try by adding the missing closing parenthesis

=Sum({$<date field = {"$(=AddMonths(date field, -1))"}>} [Amount])

or you might need the Date() function with the format

 

=Sum({$<date field = {"$(=Date(AddMonths(date field, -1), 'DateFieldFormatHere'))"}>} [Amount])
RRamos
Contributor II
Contributor II
Author

Unfortunately both suggestions didn't work.
I forgot to add that the date field is being converted to the 'fiscal' calendar, could that be causing the issue?
sunny_talwar

I don't think so, but it is easier to detect this if you are able to share a sample... 

rogerpegler
Creator II
Creator II

When you are selecting the Month are you using a different field to "date field"?  If so you should add that field to your set analysis to override the selection eg Sum({$ <date field={"$(=AddMonths(date field, -1)"}>} [Amount]) becomes

Sum({$ <date field={"$(=AddMonths(date field, -1)"}, Month=>} [Amount])

If you don't, the selection still applies and there is nothing on common with the date field set, resulting in nothing being returned.