Hi,
You might need a couple of steps to get this done.
Step 1: Count the number of days for each month
=Count([Date]) ForAll([Date]) ForEach([Month])
Step 2: Calculate total Test value for each month
=Sum([Test Value]) ForAll([Date]) ForEach([Month])
Step 3: Calculate running count for month (each month has 1 value, i.e Jan = 1, Feb = 2 and so on)
=RunningCount([Date]) ForAll ([Date]) ForEach ([Month])
Step 4: Calculate the total days of the last 12 months
Count =Count([Date]) Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month]) >=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block)-12)
Step 5: Calculate the total Test Value during the last 12 months
Sum =[Test Value] Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month])>=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block -1))
Step 6: Calculate the rolling average
=[Sum]/[Count]
Note: You might create new variables for each step above, but do not use these new variable in the calculations from step 1 to 5. All the formulas above need to be in the exact form. Otherwise the calculate context in webi will fail to generate the expected results.
I hope this helps.
Thanks,
Huu Nguyen
Add comment