Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts:
By using Set Analysis I am trying to retrieve the Previous Month Sales Data. It is Dynamic, so based on user selection, it will find out the Current Month and Previous Month and display the Sales information
E.g. year Select 2015 , Month = 1,
It should show me the Value Jan-2015 Data and Dec-2014 sales data
Similarly if year Select 2015 , Month = 2
It should show me the Value Feb-2015 Data and Jan-2015 sales data
To my understanding both the below expression should work
sum({$<Inv_YearMonth_Num = {$(=Max(Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM')))}>} Sales)
sum({$<InvYear = {$(=max(InvYear) - $(=if(max(InvMonth) = 1, 1, 0)))}, InvMonth = {$(=month(addmonths(max(Inv_YearMonth), -1)))}>} Sales)
Below are my Date and Month related field
InvYear | Inv_YearMonth | Inv_YearMonth_Num | InvMonth |
2015 | Feb-2015 | 201502 | Feb |
2015 | Jan-2015 | 201501 | Jan |
Can anyone throw some light on this issue. The above expression evaluates to 0, which implies, it is not able to get the matching information.
Appreciate an immediate reply.
Kind Regards,
Bimala
Try this:
=Sum({$<Inv_YearMonth_Num = {"$(=Date(Date#(Max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))"}, InvMonth = , Inv_YearMonth = , InvYear = >} Sales)
The above expression should show you the sales for 201501 when 201502 is selected.
Try this:
=Sum({$<Inv_YearMonth_Num = {"$(=Date(Date#(Max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))"}, InvMonth = , Inv_YearMonth = , InvYear = >} Sales)
The above expression should show you the sales for 201501 when 201502 is selected.
hi
from my experience the best way to do previous months calculation
is to add to the calendar a monthstart field that conatians for each date the month first date
then your expressions will look like this
sum({<monthstart={"$(=max(monthstart))"}>} Sales)
and for pervious month
sum({<monthstart={"$(=addmonths(max(monthstart),-1))"},Inv_year=,Inv_Month=>} Sales)
the advantage of using addmonths function that it works between years automatically
Hi Bimala
Try this: inset " " before and after calculation string
sum({$<Inv_YearMonth_Num = { " $(=Max(Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))) " }>} Sales)
sum({$<InvYear = { " $(=max(InvYear) - $(=if(max(InvMonth) = 1, 1, 0))) " }, InvMonth = { " $(=month(addmonths(max(Inv_YearMonth), -1))) " }>} Sales)
Hi Please use the below expression:
Sum({<Year=, Quarter=, Inv_YearMonth={'>=$(=max(Inv_YearMonth_Num)-1<=$(=max(Inv_YearMonth_Num))'}>} Sales)
sum({$<Inv_YearMonth_Num = {"$(=Date(addmonths(Date#(max(Inv_YearMonth_Num),'YYYYMM'),-1),'YYYYMM'))"}>} Sales)
Something like this:
sum({$<Inv_YearMonth_Num = {"$(=Date(Max(Date#(Inv_YearMonth_Num,'YYYYMM')) - 1, 'YYYYMM'))"}>} Sales)
sum({$<Inv_YearMonth_Num = {"$(=Date(AddMonths(Max(Date#(Inv_YearMonth_Num,'YYYYMM')) - 1, -1), 'YYYYMM'))"}>} Sales)
But it is much simpler if you create a proper QV date field when loading:
LOAD
...
Inv_YearMonth,
Date(Date#(Inv_YearMonth, 'MMM-YYYY')) As YMDate,
...
The expressions then are:
sum({$<YMDate = {"$(=Date(Max(YMDate) - 1))"}>} Sales)
sum({$<YMDate = {"$(=AddMonths(Max(YMDate) - 1, -1))"}>} Sales)
Thanks so much for the insight.
I changed the original expression to below and it worked fine. I had to change to 1 instead of $ and put "". the below expression works fine.
sum({1<Inv_YearMonth_Num = {"$(=Date(Date#(max(Inv_YearMonth_Num),'YYYYMM')-1,'YYYYMM'))"}>} Sales)
Hi
I need to calculate sum(sales) - sum(sales) of prev month for 6 months in pivot table. Expression to calculate previous month, contains max() .due to which the max month is always selected. I want to get sum(sales) for previous months without using max(). Can u help me for this?