I need to make a table where by i want my dimension to have the averages for last 3, last 5 and last 7 values. If the count of dimension is less than the required number of values it should display "NA" e.g. if count is less than 7 for a dimension it should display as "NA" for that dimension. I preferably need this to work in load script but open for suggestions.
The screenshot above is the scenario that i am, talking about. Any help is appreciated.
This is an alternative approach to resolve your challenge, taking into account that:
Nothing define your data cronologically, so I basically added a row number to you data.
I also added a support column to your raw data, basically counting the number of times we recorded an score against a player, the AutoNumber(RowNo(), Player) does this calculation, I named the column Seq.
Now with these two new columns, we creae an Averages table calculating the Score Sum when the Player's scores appeared 3, 5 and 7 times; creating a column for each scenario: Sum_Last_3, Sum_Last_5 and Sum_Last_7; We also count the number of times the Seq had been 3, 5 and 7 per player.
Finnaly, We create the Result table, with 3 columns: Avg_L3, Avg_L5 and Avg_L7 using the Last_x columns to tell us when to return 'na' or the Average.
This is the screenshot of the solution:
This is the Load Script of the solution:
Load RowNo() As Row, *, AutoNumber(RowNo(), Player) As Seq Inline [
Sum_Last_3 / 3 As Avg_Last_3,
Sum_Last_5 / 5 As Avg_Last_5,
Sum_Last_7 / 7 As Avg_Last_7,
Sum(If(Seq<=3, Score, 0)) As Sum_Last_3,
Sum(If(Seq<=5, Score, 0)) As Sum_Last_5,
Sum(If(Seq<=7, Score, 0)) As Sum_Last_7,
Sum(If(Seq<=3, 1, 0)) As Last_3,
Sum(If(Seq<=5, 1, 0)) As Last_5,
Sum(If(Seq<=7, 1, 0)) As Last_7
Load Player As r_Player,
if(Last_3 = 3, Avg_Last_3, 'na') As Avg_L3,
If(Last_5 = 5, Avg_Last_5, 'na') As Avg_L5,
If(Last_7 = 7, Avg_Last_7, 'na') As Avg_L7
I also attached the Z_Avg_Last_3Values_01.qvf file.
Hope this helps,
Arnaldo Sandoval A journey of a thousand miles begins with a single step.