Working out Averages

[#1]

Hi there,

Im trying to get a table to show a players successful outcomes.I have this line of code which works  and gives me a percentage of positive outcomes from a specific match but I also want a line to show the players average in general and also the average success rate of all players in a specific match.

The code to show successful outcomes is as follows:

({SAR_1<[Kick Outcome]={1}>}DISTINCT MatchID_ScenarioMap)/count({SAR_1}DISTINCT MatchID_ScenarioMap)

The matches are stored as 'MatchID' and the players by 'Kicker', I was thinking that I would need to put in a modifier to exclude either the kicker from the code or the match for each average but it doesn't seem to work.

any Help is greatly appreciated,

Rob

[morganaaron]

Try using the TOTAL qualifier in your first count statement to ignore a given dimension, in this case Kicker and MatchID separately.

You'll want to use TOTAL <Kicker> when wanting the Kickers average, as this tells Qlikview to calculate the TOTAL but to still calculate over the Kicker dimension, and vice versa for the MatchID.

[#3]

Yes that could although im a little unsure where to place the <Kicker>, could you perhaps right out the code as an example for me! 

[morganaaron · Accepted solution]

With Kicker and MatchID as your dimensions, for the Kickers average try:

Count({SAR_1<[Kick Outcome]={1}>}TOTAL <Kicker> DISTINCT MatchID_ScenarioMap)/Count({SAR_1} TOTAL <Kicker> DISTINCT MatchID_ScenarioMap)

[#5]

thanks very much, that works great!