Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear experts,
in my report I have to calculate "Std." for the last 3 Month --> Feb, Jan, Dez, Nov.
I have problems by changing of the year.
In my global variable (Variablenliste) I calculate the past month:
PreviousMonth_1
if(((num($(CurrentMonth)-1))='0' or num($(CurrentMonth))-1)='-1' or (num($(CurrentMonth)-1))='-2', num($(CurrentMonth)-1)+12, num($(CurrentMonth)-1) )
PreviousMonth_2
if((num(month(today())-2))='-1' or (num($(CurrentMonth)-2))='-2' or (num($(CurrentMonth)-2))='0', num($(CurrentMonth)-2)+12,num($(CurrentMonth)-2) )
PreviousMonth_3
if((num(month(today())-3))='-1' or (num($(CurrentMonth)-3))='-2' or (num($(CurrentMonth)-3))='0', num($(CurrentMonth)-3)+12,num($(CurrentMonth)-3) )
In my diagram (pivottable) I use follow formel:
(sum({< Month = {"$(=$(PreviousMonth_3) )"}>} Std)+
sum({< Month = {"$(=$(PreviousMonth_2) )"}>} Std)+
sum({< Month = {"$(=$(PreviousMonth_1) )"}>} Std))
This is working wrong, because I don't change the year: =num(Year(today()))-1
In my data I have records for 2015 only for November and Dezember. By other calculation I do:
if (Year='2015',
(sum({< Month = {"$(=max(Month))"}>} Std)),
(sum({< Month = {"$(=min(Month)+11)"}>} Std))
)
How can I calculate the sum of the last 3 month including change of the year?
Do you have another suggestions ?
Kind regards,
Hi,
Try like this
Data:
LOAD
Year, Month, Std,
MakeDate(Year, Month) AS Date
INLINE [
Year, Month, Std
2015,11, 10
2015, 12, 15
2016, 01,15
2016, 02, 20];
And use below expression
Sum({<Year=, Month=,
Date={‘>=$(=MonthStart(Max(Date), -3))<=$(=MonthEnd(Max(Date), -1))’}>} Std)
Hope this helps you.
Regards,
jagan.
Possible to share a sample? It would be much easier and faster to help you that ways
Hi Sunny,
Example: I have following table:
Year, Month, Std
2015,11, 10
2015, 12, 15
2016, 01,15
2016, 02, 20
I want to have Sum of Std for the last 3 mont: 20+15+15
My Formel is doing: 2016-02 + 2016.01 + 2016.12
The month is correctly calculated, but not the year:
The month I calculate as follow:
PreviousMonth_1
if(((num($(CurrentMonth)-1))='0' or num($(CurrentMonth))-1)='-1' or (num($(CurrentMonth)-1))='-2', num($(CurrentMonth)-1)+12, num($(CurrentMonth)-1) )
and Sum:
(sum({< Month = {"$(=$(PreviousMonth_3) )"}>} Std)+
sum({< Month = {"$(=$(PreviousMonth_2) )"}>} Std)+
sum({< Month = {"$(=$(PreviousMonth_1) )"}>} Std))
Hi,
If you have a date field in your Data model then you can try below expression.
Sum({<Year=, Quarter=, Month=, Week=,
Date={‘>=$(=MonthStart(Max(Date), -3))<=$(=MonthEnd(Max(Date), -1))’}>} Sales )
Check this link for lot of similar expressions.
Set Analysis for Rolling Periods
Hope this helps you.
Regards,
jagan.
Hi,
Try like this
Data:
LOAD
Year, Month, Std,
MakeDate(Year, Month) AS Date
INLINE [
Year, Month, Std
2015,11, 10
2015, 12, 15
2016, 01,15
2016, 02, 20];
And use below expression
Sum({<Year=, Month=,
Date={‘>=$(=MonthStart(Max(Date), -3))<=$(=MonthEnd(Max(Date), -1))’}>} Std)
Hope this helps you.
Regards,
jagan.
Dear Jagan,
thank you for your answer. It was helpful. I have modified your formel:
--> AddMonths(MonthStart(Max(Date)),-2), because this one is only substract the Days and not the month: MonthStart(Max(Date), -3):
--> Sum({<Year=, Month=, Date={">=$(=AddMonths(MonthStart(Max(Date)),-2))<=$(=MonthEnd(Max(Date)))"}>} Std))
No need of AddMonths() when using MonthStart() Or MonthEnd(). This is the best one
Sum({<Year=, Month=, Date={">=$(=MonthStart(Max(Date),-2))<=$(=MonthEnd(Max(Date)))"}>} Std)
Hope this helps you.
Regards,
jagan.