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.
