topic Re: Set Analysis Prior Month in QlikView

Set Analysis Prior Month

aetingu12 — Wed, 05 Sep 2018 15:33:35 GMT

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

Re: Set Analysis Prior Month

rajivmeher — Wed, 05 Sep 2018 15:40:31 GMT

Hi aetingu12‌

Please try the following:

Sum({< Date={">= AddMonths(MonthStart(Max(Date)),-1)<MonthStart(Max(Date))"} Sales>})

Regards

Rajiv.

Re: Set Analysis Prior Month

aetingu12 — Wed, 05 Sep 2018 15:52:25 GMT

Not sure if the code you helpfully provided is correct?

Just to clarify, the Date field is always the last day of the month. There won't be any dates between the first and the last, just the 31 first.

Thanks again.

Re: Set Analysis Prior Month

joseph_eftamand — Wed, 05 Sep 2018 15:54:37 GMT

Are you trying to look at the last day of the previous month? that's what monthend will do.

Without testing it looks like you may be missing $():

Calculate max date in a variable

=max(Date)

then in set analysis use :

=Sum({$<Date ={'$(=monthend(AddMonths('$(vmaxDate)', -1))'}>} Sales)

I would actually just create a flag in the script, for example:

if(Month(Date) = Month(AddMonths(Today(),-1),1,0)

Or if you need the max(Date) rather than today() just create a temp table:

Temp:

Load

Max(Date) as max_Date,

min(Date) as min_Date

Resident <Main Fact>;

LET vMaxDate = peek('Temp',0,max_Date);

Create flag:

if(Month(Date) = Month(AddMonths($(vMaxDate),-1),1,0) as previous_month_flag

Then your set analysis can simply be: sum({<previous_month_flag= {1} >} Sales)

Hope that makes sense.

Re: Set Analysis Prior Month

rajivmeher — Wed, 05 Sep 2018 16:14:52 GMT

Hi aetingu12‌

Thanks for the feedback. Would it be possible to provide some sample data?

Alternatively please try the following:

=sum({<Date= {"=DATE(MonthEnd(ADDMONTHS(MAX(DATE), -1)), 'YYYY-MM-DD')"} >} Sales)

Please change 'YYYY-MM-DD' to the format your have your data in. The issue seems to be MonthEnd function changes the format to datetime, where as you might have data in only date format.

Sorry, if my answer is different from your requirement.

Regards

Rajiv.

Re: Set Analysis Prior Month

aetingu12 — Wed, 05 Sep 2018 16:24:09 GMT

Thank you for this.

I thought it would we a very simple task with just addmonths(max(date),-1) script. All my dates are monthends, so yes I should not have included in my question.

Dates are 31-03-2018, 30-04-2018, 31-05-2018 etc.

Re: Set Analysis Prior Month

joseph_eftamand — Wed, 05 Sep 2018 16:35:09 GMT

Are you still having trouble with the dates? You may need to do some work on the formatting. If the above solution worked can you mark the answer as correct? Thanks.

Re: Set Analysis Prior Month

rajivmeher — Wed, 05 Sep 2018 17:08:36 GMT

Hi aetingu12‌

Based on your sample data the following should work fine.

=sum({<Date= {"=DATE(MonthEnd(ADDMONTHS(MAX(DATE), -1)), 'DD-MM-YYYY')"} >} Sales)

Let me know if this works.

Regards

Rajiv.

Re: Set Analysis Prior Month

aetingu12 — Thu, 06 Sep 2018 07:22:16 GMT

Unfortunately yes;

When I try just to get the correct date:

=AddMonths(max(Date),-1)

I do get the correct date, 30/06/2018. (Max date is 31/07/2018). For some reason, I just can not insert it into my expression to get the sales numbers.

Basically if I did: = Sum({<Date = {"30/06/2018"}>} Sales) it works. It is just when I replace the string with the script. Even when I convert it to Date as:

=Date(AddMonths(max(Date),-1),'DD/MM/YYYY') it doesnt work.

Appreciate all your help and patience.

Re: Set Analysis Prior Month

rajivmeher — Thu, 06 Sep 2018 08:02:23 GMT

Thanks aetingu12‌.

Will it be possible to share QVW with some sample data? It will be easier to troubleshoot the exact issue that way.

Regards

Rajiv.

Re: Set Analysis Prior Month

ebrahimaljafri — Thu, 06 Sep 2018 08:24:25 GMT

=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

Re: Set Analysis Prior Month

frank_dehner — Thu, 06 Sep 2018 08:37:42 GMT

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:

Get a distinct list of the calendar element
Add an order criteria within a sorted list of this distinct element --> the "rank"
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!

Re: Set Analysis Prior Month

aetingu12 — Thu, 06 Sep 2018 10:13:02 GMT

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

Re: Set Analysis Prior Month

aetingu12 — Thu, 06 Sep 2018 17:44:43 GMT

‌brilliant idea!:))