Average of last 3 values of dimension

[mansoorsheraz]

Hi,

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.

Regards.

[Ksrinivasan]

hi,

use this measure in straight table

=count({$<Rank1= {">4"}, Score={">99"}>}Player)

ksrinivasan

[mansoorsheraz]

Thanks but this is not what i was looking for in the load script. I wanted the resultant values for the averages to be there in the load script so that they came in as pre calculated values and only drop them on the visualization will do the job. Hope you get me. I have tried using the subfield in the load script but its not working.

[mansoorsheraz]

Never mind @Ksrinivasan I have been able to figure it out in the load script using the GROUP BY Clause. I will get back to you if i come up with this ever demanding task 🙂

[Ksrinivasan]

hi,

LOAD
Player,
Score,
AutoNumber(RowNo(),Player) as Rank1
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is Criket);

TTT:
LOAD *,(if(match(Rank1,1,2,3),(Score))) as AVG_3,
(if(match(Rank1,1,2,3,4,5),(Score))) as AVG_5,
(if(match(Rank1,1,2,3,4,5,6,7),(Score))) as AVG_7


Resident [Criket]
Order by Player,Rank1 Asc;

ksrinivasan