Averaging problem

[NickP_DF]

Hello guys,

I need help in calculating averages; pls have a look at my example: in the upper table I show all the participants to a championship and in the lower one I’d like to show the averages of them.

The  averages for every season must be calculated in this way: Season number of participants of each age / Number of races of the season

The  total averages must be calculated in this way: Total number of participants of each age / Total number of races of the seasons

I currently use this formula: Sum(PartecipantiRP)/Count(IdGaraRP)

But It’s wrong because Qlik considers only the races where has been at least one participant and not all the potential race for every season.

Should anybody help me with the right formula, pls?

Thanks.

N.

[Frank_Hartmann]

try this expression

=If(TipoAnalisi='M', Sum(PartecipantiRP)/Count(DISTINCT IdGaraRP), Sum(PartecipantiRP))

[NickP_DF]

Hello, Frank

thanks for the help: unfortunately, it gives the same result as the previous version.

N.

[Frank_Hartmann]

please share expected output

[Brett_Bleess]

@Frank_Hartmann I just wanted to alert you that @NickP_DF  appears to be running version 10 here, so there may be some other issues in play on this one.  Nick, if you can help Frank out with a screenshot of what you expect to see in the output that will likely help, I did have a look at the app, but I am not sure what you really want as well, so a further update on things should help you get some further help.

Regards,
Brett

[NickP_DF]

Hi Brett,

Thank you for the support. I'm not able to access to a computer non a day.

I'll prompt you asap.

Thanks again.

Nick

[NickP_DF]

...here I am!

In the attached Excel file you can see in yellow the expected avg (given by the sum of participants of each race divided by the number of races of every season) and the complessive average (given by the same criteria).

As I explained before, the problem is that my formula Sum(PartecipantiRP)/Count(IdGaraRP) gives the number of every season races NOT equal to the total races of every season, but only by the races where has been at least one participant (excluding the potential races where nothing of the participant has arrived).

Hope It’s much clear now.

Thanks.

N.

@Brett_Bleess  : Pls note that the mistake is independent from the QV version; the 12 one gives the same result

[Frank_Hartmann · Accepted solution]

Maybe like attached.

hope this helps

[NickP_DF]

Hello Frank,

thank you for the smart solution you've found and excuse me for the delay of the answer.

I've studied your solution and I've tried to replicate it on my main sheet, with several years and several categories, but I've found two problems:

- what is "dim" that you used in your formula? (I'm not able to understand if it is a variable of what else and in my new sheet it is unknown).

- is it possible to find a solution which doesn't need to enumerate the years in the formula (so that it will be ready also for next years without modify it)?

Thank you so much.

N.

[Frank_Hartmann]

Hi Nick,

look at the added lines of script in the qvw of my prvious post. this is called island table. in essence, it gives you the option to apply different expressions to different rows, taking different dimension values into account.

with this technique you will have to hardcode the years. i dont see another option!