Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
aetingu12
Creator
Creator

Set Analysis Prior Month

Dear all,

I have this set analysis:

=sum({<Date= {"=monthend(addmonths(max(Date),-1))"} >} Sales)

For some reason it is always giving me the total Sales and not the month prior to the last available month.  I must be making a fundamental mistake in my code.

Regards,

Aksel

13 Replies
ebrahimaljafri
Partner - Contributor III
Partner - Contributor III

=sum({<Date= {">=$(=AddMonths(max([Date]),-1))<=$(=month(max(([Date]))))"} >} Sales)

kindly check Date field is recognized by qlik as field not function and try above set analysis

frank_dehner
Contributor II
Contributor II

Hi Aksel,

We often have the same problem, e.g., pre-select the last 3 days or the last complete week or compare last month with month before and so on. Therefor we have a calendar qvd with all the valid day-week-month-quarter- and year relations. After loading the needed parts from this qvd we calculate a rank for each interesting calendar value: The current period gets rank "0", the period before gets "-1" and so on.

Calendar with rank.png

The set analysis gets much easier with this: Only({$<Month_Rank={"-1"}>} Month) gets the last month...

To calculate the rank of a period you need three steps:

  1. Get a distinct list of the calendar element
  2. Add an order criteria within a sorted list of this distinct element --> the "rank"
  3. Join the rank into your calendar

Typical code looks like this:

Calendar with rank - code snippet.png

You can do this for each period like "Week", "Month", ... If you want you can move this code snippet into a sub-routine and call it for each of your periods...

Have fun!

aetingu12
Creator
Creator
Author

Thank you for this.

This is giving me the sum of the last two months:)

Not the prettiest solution but with a little bit of tweak on the solution from ebrahimaljafri solved the issue.

=sum({<Date= {">=$(=AddMonths(max(Date),-1))<=$(=AddMonths(max(Date),-1))"} >} Sales)

I guess the question is, with set analysis we need to use either <= or >=, unless we script our variable.

The correct way to handle this should be in line with this article on Dates in Set Analysis

aetingu12
Creator
Creator
Author

‌brilliant idea!:))