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.

[mansoorsheraz]

can anyone address my issue ?

[Ksrinivasan · Accepted solution]

hi,

try this, Expressions are in header,

ksrinivasan

[mansoorsheraz]

Hi, 

Thanks for your time to reply to my query. Actually there are two issues with this :

1) the extra column coming for concat(score) , all depends on it . If i cannot get rid of it then how can i hide it ?

2) i wanted some solution in the load script so that there is a resultant table with all this information.

Can you help me with it ?

Thanks again.

[Ksrinivasan]

hi,

i have removed extra concat(score) and also eliminate dependency of this column,

i will try get it from script,

you also try,

ksrinivasan

[Ksrinivasan]

hi,

as i assured, i have created Load script and pivot Table expression for you,

Below Load Script:

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

and Table as below, it has Table expression,

Ksrinivasan.

[ArnadoSandoval]

Hi @mansoorsheraz 

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:

Players:
Load RowNo() As Row, *, AutoNumber(RowNo(), Player) As Seq Inline [
Player, Score
Kohli, 100
Kohli, 97
Kohli, 134
Kohli, 123
Kohli, 122
Kohli, 110
Kohli, 88
Rohit, 67
Rohit, 87
Rohit, 6
Rohit, 44
Rohit, 5
Ashwin, 9
Ashwin, 12
Ashwin, 23
Ashwin, 12
Ashwin, 33
Ashwin, 12
];

NoConcatenate
Averages:
Load Player,
     Sum_Last_3 / 3 As Avg_Last_3,
     Sum_Last_5 / 5 As Avg_Last_5,
     Sum_Last_7 / 7 As Avg_Last_7,
     Last_3,
     Last_5,
     Last_7
;
Load Player,
     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
Resident Players
Group By
	Player;

NoConcatenate

Result:
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
Resident Averages;

I also attached the Z_Avg_Last_3Values_01.qvf file.

Hope this helps,

[mansoorsheraz]

Thanks for your reply. How can I do the following here:

Count 100's in last three innings i.e. Kohli 2, Rohit 0, Ashwin 0

Can you help me with it ?

[Ksrinivasan]

hi,

below script

Criket:
LOAD
Player,
Score,
AutoNumber(RowNo(),Player) as Rank1,
if(AutoNumber(RowNo(),Player)>'4' and Score >'100','1','0') as Above_100

result:

ksrinivasan

[mansoorsheraz]

Thanks for your reply but how can i achieve it in the straight table and not in the load script ?