topic Re: Calculation of the last past month in QlikView

Calculation of the last past month

Thu, 03 Mar 2016 16:03:46 GMT

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,

Re: Calculation of the last past month

sunny_talwar — Thu, 03 Mar 2016 17:35:53 GMT

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

Re: Calculation of the last past month

Fri, 04 Mar 2016 08:36:00 GMT

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

Re: Calculation of the last past month

jagan — Fri, 04 Mar 2016 08:50:56 GMT

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.

Re: Calculation of the last past month

jagan — Fri, 04 Mar 2016 08:52:36 GMT

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.

Re: Calculation of the last past month

Fri, 04 Mar 2016 14:02:20 GMT

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

Re: Calculation of the last past month

jagan — Mon, 07 Mar 2016 00:45:31 GMT

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.