Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NickP_DF
Creator II
Creator II

Averaging problem

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.

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Maybe like attached.

hope this helps

 

View solution in original post

10 Replies
Frank_Hartmann
Master II
Master II

try this expression

=If(TipoAnalisi='M', Sum(PartecipantiRP)/Count(DISTINCT IdGaraRP), Sum(PartecipantiRP))
NickP_DF
Creator II
Creator II
Author

Hello, Frank

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

N.

Frank_Hartmann
Master II
Master II

please share expected output

Brett_Bleess
Former Employee
Former Employee

@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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
NickP_DF
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

Maybe like attached.

hope this helps

 

NickP_DF
Creator II
Creator II
Author

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
Master II
Master II

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!