topic Re: Working out Averages in QlikView

Working out Averages

Fri, 31 Oct 2014 10:44:39 GMT

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

Re: Working out Averages

morganaaron — Fri, 31 Oct 2014 10:51:26 GMT

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.

Re: Working out Averages

Fri, 31 Oct 2014 10:54:07 GMT

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!

Re: Working out Averages

morganaaron — Fri, 31 Oct 2014 11:04:28 GMT

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)

Re: Working out Averages

Fri, 31 Oct 2014 11:09:36 GMT

thanks very much, that works great!