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

[rajivmeher]

Hi aetingu12‌

Please try the following:

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

Regards

Rajiv.

[aetingu12]

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.

[joseph_eftamand]

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]

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]

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.

[joseph_eftamand]

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]

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]

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]

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.