7 Replies Latest reply on Nov 2, 2017 10:58 AM by haitham Farouk

# Rolling 12 - Problem

Hi All,

I am trying to develop a report that will show JUST the Rolling 12 Average Amount  for the last month and also once I get this ,  I would like to do the same for  the R12 Amount for the prior month and the prior year.

I was able to create calculation to show the  Amount from the last  month,   but I can't get to work the R12 Amount Calculation.

I hope this make sense . Please also see the attached report.

Thank you!

• ###### 1. Re: Rolling 12 - Problem

Hi Kalen,

You can use similar calculation and adjust the starting and ending month for the average calculation

fro example

[Amount 12 (Prev Period)]

WINDOW_AVG(SUM([Amount]), -12, -1)

This mean start by the last month and move back 12 month, also for year

[Amount 12 (Last Year)]

WINDOW_AVG(SUM([Amount]), -23, -12)

the important thing to have enough data in you data set (month).

Best Regards

• ###### 2. Re: Rolling 12 - Problem

Hello,

thanks for the reply, however If you open up  the attached file you can see that i kinda already do that in R12_Amount calculation.

My problem is to plug the last month R12 number into the circle in the Screenshot below.

This is my calculation there that doesn work

IF

(

DATETRUNC([Compare by],MAX([Date])) =  DATETRUNC([Compare by],max([Pick a Date]))

and

DATETRUNC('month',MAX([Date])) =  DATETRUNC('month',max([Pick a Date]))

)

THEN

[R12_Amout]

END

• ###### 3. Re: Rolling 12 - Problem

Hi, Kalin

Is this what you are after?

ZZ

• ###### 4. Re: Rolling 12 - Problem

Hi Kalen,

Ok, you are trying to use table calculation without include the calc dimension (Month) ... you can do the following:

1- drag the date into detail mark as follow:

2- Change your table calculation to be as follow

3- Add your table calculation to size mark to get the last month calculated value as follow:

Note:

Best Regards

1 of 1 people found this helpful
• ###### 5. Re: Rolling 12 - Problem

This is great ! I can't thank you enough

• ###### 6. Re: Rolling 12 - Problem

Here's the final version - it might be helpful for somebody.

• ###### 7. Re: Rolling 12 - Problem

u r welcome