Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation of the last past month

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,

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
sunny_talwar

Possible to share a sample? It would be much easier and faster to help you that ways

Not applicable
Author

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))

jagan
Luminary Alumni
Luminary Alumni

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.

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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))

jagan
Luminary Alumni
Luminary Alumni

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.