Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

aetingu12
Contributor

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

Tags (1)
1 Solution

Accepted Solutions
aetingu12
Contributor

Re: Set Analysis Prior Month

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

13 Replies
rajivmeher
Contributor

Re: Set Analysis Prior Month

Hi aetingu12

Please try the following:

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

Regards

Rajiv.

aetingu12
Contributor

Re: Set Analysis Prior Month

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.

Partner
Partner

Re: Set Analysis Prior Month

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.

rajivmeher
Contributor

Re: Set Analysis Prior Month

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.

aetingu12
Contributor

Re: Set Analysis Prior Month

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.

Partner
Partner

Re: Set Analysis Prior Month

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.

rajivmeher
Contributor

Re: Set Analysis Prior Month

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.

aetingu12
Contributor

Re: Set Analysis Prior Month

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.

rajivmeher
Contributor

Re: Set Analysis Prior Month

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.