Rolling 3 months and 12 months calculations

[rohitraturi]

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

[Gysbert_Wassenaar · Accepted solution]

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

[rohitraturi]

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

[Gysbert_Wassenaar]

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.

[rohitraturi]

Thank You for the explanation. I appreciate that.