Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
NickP_DF
New Contributor III

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
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Averaging problem

Maybe like attached.

hope this helps

 

View solution in original post

10 Replies
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Averaging problem

try this expression

=If(TipoAnalisi='M', Sum(PartecipantiRP)/Count(DISTINCT IdGaraRP), Sum(PartecipantiRP))
Highlighted
NickP_DF
New Contributor III

Re: Averaging problem

Hello, Frank

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

N.

Highlighted
Frank_Hartmann
Honored Contributor II

Re: Averaging problem

please share expected output

Highlighted
Digital Support
Digital Support

Re: Averaging problem

@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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
Highlighted
NickP_DF
New Contributor III

Re: Averaging problem

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


Highlighted
NickP_DF
New Contributor III

Re: Averaging problem

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

Highlighted
Frank_Hartmann
Honored Contributor II

Re: Averaging problem

Maybe like attached.

hope this helps

 

View solution in original post

Highlighted
NickP_DF
New Contributor III

Re: Averaging problem

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.

Highlighted
Frank_Hartmann
Honored Contributor II

Re: Averaging problem

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!