Last 30 day average

[nesawant]

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

[sunny_talwar]

You can use Above/Below functions with RangeAvg().

Missing Manual - Above() and Below()

or Before/After if the Dates are pivoted

Missing Manual - Before() and After()

[#3]

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.

[nesawant]

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.