topic Re: Averaging problem in QlikView

Averaging problem

NickP_DF — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Averaging problem

Frank_Hartmann — Sun, 20 Oct 2019 11:14:57 GMT

try this expression

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

Re: Averaging problem

NickP_DF — Sun, 20 Oct 2019 15:17:07 GMT

Hello, Frank

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

Re: Averaging problem

Frank_Hartmann — Sun, 20 Oct 2019 18:19:31 GMT

please share expected output

Re: Averaging problem

Brett_Bleess — Tue, 29 Oct 2019 11:59:41 GMT

@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

Re: Averaging problem

NickP_DF — Tue, 29 Oct 2019 13:26:36 GMT

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

Re: Averaging problem

NickP_DF — Sat, 02 Nov 2019 17:20:09 GMT

...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.

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

Re: Averaging problem

Frank_Hartmann — Sun, 03 Nov 2019 21:33:42 GMT

Maybe like attached.

hope this helps

Re: Averaging problem

NickP_DF — Sun, 10 Nov 2019 14:34:54 GMT

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.

Re: Averaging problem

Frank_Hartmann — Mon, 11 Nov 2019 09:39:34 GMT

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!

Re: Averaging problem

NickP_DF — Sun, 17 Nov 2019 16:21:44 GMT

Hi Frank,

I've seen the "Island table".

A great solution!

Thank you so much.

Nick.