# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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,

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Calculation of the last past month

Hi,

Try like this

Data:

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.

6 Replies
MVP

## Re: Calculation of the last past month

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

Not applicable

## Re: Calculation of the last past month

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

MVP

## Re: Calculation of the last past month

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.

MVP

## Re: Calculation of the last past month

Hi,

Try like this

Data:

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

## Re: Calculation of the last past month

Dear Jagan,

--> AddMonths(MonthStart(Max(Date)),-2), because this one is only substract the Days and not the month: MonthStart(Max(Date), -3):

MVP

## Re: Calculation of the last past month

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.