Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rohitraturi
New Contributor II

Rolling 3 months and 12 months calculations

I need to calculate the amount for rolling 3 months and 12 months.

I have created a MonthIndex in my calendar script

Year($(v_MinDate) + RecNo() - 1) as Y,

(Y*12) + Num(M) as [Posting MonthIndex],

 

Using this MonthIndex I am doing the calculations however it works only for a year at a time.

Example: if today's date is 3rd Feb then I should get entire Dec 2018, entire Jan 2019 and the 3 days of Feb 2019. However it gives me entire Jan 2019 and 3 days of Feb 2019.

//Rolling 3mo
sum({< Corrected ={'N'}, [TX Type] = {'C'}
, [Posting MonthIndex] = {">=$(=MAX([Posting MonthIndex])-2)<=$(=Max([Posting MonthIndex]))"}
>} [Amount])
-
sum(
{<Corrected ={'Y'},[TX Type] = {'C'}
, [Posting MonthIndex] = {">=$(=MAX([Posting MonthIndex])-2)<=$(=Max([Posting MonthIndex]))"}
>} [Amount])
//---------------------------------------------------------------------------------------------
//Roling 12 mo
sum(
{< Corrected ={'N'}, [TX Type] = {'C'}
, [Posting MonthIndex] = {">=$(=MAX([Posting MonthIndex])-11)<=$(=Max([Posting MonthIndex]))"}
>} [Amount])
-
sum(
{<Corrected ={'Y'}, [TX Type] = {'C'}
, [Posting MonthIndex] = {">=$(=MAX([Posting MonthIndex])-11)<=$(=Max([Posting MonthIndex]))"}
>} [Amount])

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Rolling 3 months and 12 months calculations

Try: AutoNumber(Y*12 + Num(M)) as [Posting MonthIndex]

talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: Rolling 3 months and 12 months calculations

Try: AutoNumber(Y*12 + Num(M)) as [Posting MonthIndex]

talk is cheap, supply exceeds demand
rohitraturi
New Contributor II

Re: Rolling 3 months and 12 months calculations

It Worked. I did not understand why to use AutoNumber(). Can you provide some explanation?

MVP & Luminary
MVP & Luminary

Re: Rolling 3 months and 12 months calculations

You were creating numbers like 201902, 201901, 201812 etc. If you subtract 3 from 201902 you get 201899. And 201899 does not match any value in you data. There's no month 99 so you never had a value 201899 in your data. If you autonumber the values you get sequential numbers like 1,2,3,... etc. So November 2018 for example could have the number 24 and then Februari 2019 would have number 27. If you take the number of Februari 2019, which is 27, and substract 3 then you get 24, which is the number for November 2018.


talk is cheap, supply exceeds demand
rohitraturi
New Contributor II

Re: Rolling 3 months and 12 months calculations

Thank You for the explanation. I appreciate that.