Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create a pivot in like:
Date | Count(Activity) | Last 30 day average |
---|---|---|
29-Aug-2016 | Count(Activity) on 29th Aug | Count(Activity) for last 30 days/30 |
28-Aug-2016 | Count(Activity) on 28th Aug | Count(Activity) for last 30 days/30 |
& so on...
I am unable to get last 30 day average right. The problem is due to Date being used as dimension.
If I make a calendar object & select a date & use any other dimension, the calculation turns out to be right.
Can someone help?
Best Regards,
Neha
You can use Above/Below functions with RangeAvg().
Missing Manual - Above() and Below()
or Before/After if the Dates are pivoted
Hi Neha,
There are basically 2 ways:
1. pls add an indicator in your Calender/fact table each of the last 30 days. like:
if(today() - Fact-Date > 30, 0,1) as 30DayAvg_Ind now you marked your records which are valid for the AVG calculartion
now you can calculate the proper average with a formula somewhat like:
=AVG(${ <30DayAvg_Ind={'1'} >} Counter), this wil do the trick in a formula.
2.
Calculate the averages of all your dimensions in the script with the group by statement:
and add these averages to your facts with join or applymap...
like:
30DayAVG: NoConcatenate
resident Facts
Key,
avg(Counter) as Average_Dim1
where 30DayAvg_Ind group by dimension1; (this should be done for every dimension)
and add these averages to all the facts records with a left join or applymap
Than you are able to display the created average field (Average_Dim1)from your datamodel into your table
good luck and HTH
P.
Hi,
This pivot is not being calculated for all dates. I have a calendar object which is used to select a date & the pivot needs to be calculated only for that date.