I am trying to resolve a metric where I have to compute a Rolling 90 days average for each week. The formula needs to determine if it has a complete 90 days. We do not have access to the Universe level because we are using Rich Client 4.1. We also need to be able to use input controls in the report on these 90 day measurements.
For Example:
Report Date: 4/1/2015 Name Metric Date Range Average Calculation Bob 7.5 1/1/2015-4/1/2015 Billly 9.4 1/1/2015-4/1/2015
Report Date 4/8/2015 Name Metric Date Range Average Calculation Bob 7.8 1/8/2015-4/8/2015 Billly 9.9 1/8/2015-4/8/2015
Note: The "metric" is an average of the 90 date range. We store our information as follows: Date Name Metric 4/8/2015 Bob 7 4/7/2015 Bob 16 4/6/2015 Bob 5 4/5/2015 Bob 12 4/4/2015 Bob 2 4/3/2015 Bob 11 4/3/2015 Bob 5 4/1/2015 Bob 10 ....
Whats the aggregation given in Universe? If it is sum then create a variable in webi v_aggmetric = avg(measue), then insert for the names. Webi will aggregate on its own.
Other option would be to insert a group by on the custom sql of query.
We need to be able to create the average based on 90 days from the 'date' in the example below. So it would be 90 day average on 4/8, 4/9,...
Date Name Metric
4/8/2015 Bob 7
4/7/2015 Bob 16
4/6/2015 Bob 5
4/5/2015 Bob 12
4/4/2015 Bob 2
4/3/2015 Bob 11
4/3/2015 Bob 5
4/1/2015 Bob 10
The issue is that we cannot filter for those dates, we need to display all days and their corresponding 90 day average. Any ideas on how to reference 90 days from the 'date' column as an average?
Thank you for that formula. This is actually the one I have found in the forums and tried to manipulate dozens of different ways to make it work for what we need. The issue with this one is that as soon as you remove the date from the view the formula no longer works. Because we will eventually hide the date and place the resulting metric on a view that only shows one report date via prompt when we run the report. But that single date needs to show is the average for the last 90 days.
I tried incorporating foreach, where, between, but none of the functions have made it to what we need unfortunately.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.