Set Analysis Prior Month

[aetingu12]

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

[ebrahimaljafri]

=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]

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.

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:

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 · Accepted solution]

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]

‌brilliant idea!:))