topic Show "Others" Row - Problem w/Averages in QlikView

Show "Others" Row - Problem w/Averages

Sat, 22 Jan 2011 00:20:14 GMT

Hello,

In my straight table I'm creating a list of top 'X' rows. I've set the expressions to "Show Others Row". For sums no problem, you set the expressions Total Mode to "SUM" and it works great. The problem I'm encountering is with my "Others" data that is not sums but averages. I set the Total Mode to "AVERAGE" and it gives me the sum not the average of all other rows (see screen-print below).

Has anybody else encountered this? Is this a bug or is there a workaround?

Thanks

Show "Others" Row - Problem w/Averages

Sat, 22 Jan 2011 17:32:12 GMT

Please provide the sample data to try for a resolution to fix this issue.

Show "Others" Row - Problem w/Averages

Anonymous — Mon, 24 Jan 2011 13:07:24 GMT

Isn't the blue line your "Total" line?
Like IT User suggest, share the documnet then we can have a closer look.

Show "Others" Row - Problem w/Averages

Mon, 24 Jan 2011 18:10:10 GMT

Thanks for the response, I've created a very simple qvw to demo this behavior. See below. The first table just shows the data. The second I'm creating the "Others" row which is not averaging the "Other" 2 rows correctly.

I hope this clearly demonstates the issue.

Show "Others" Row - Problem w/Averages

Anonymous — Mon, 24 Jan 2011 18:54:24 GMT

I see what you mean and what you expact, but with the option "Show Other Rows" is not a "Total".

The "Show Other Rows" option gives you the sum of the results of all the other values which are not in you "Max_number" rows.

Show "Others" Row - Problem w/Averages

Mon, 24 Jan 2011 20:31:07 GMT

Yep that's the problem, it's summing the "Others" row vs. averaging it. Can you think of a work-around? I can't. If I show a value for the "Others" row on my Average column the users will be confused because the number will be very high and misleading. The only thing I can think of is adding the columns that make up the Average column and forcing them to calculate the "Others" average themselves in Excel or manually.

Any other suggestions?

Thanks - DB

Show "Others" Row - Problem w/Averages

Anonymous — Mon, 24 Jan 2011 20:43:52 GMT

Suggestions? This depends on how you want to use it.

1. If there is a fixed amount of rows showed (max number) you could make the calculations already in the load script.

2. If you think showing the "Others" row is confusing you can just drop it by removing the check before "Show Other rows" But I don't know if that is an option.

I will try to come up with other suggestions later....

Show "Others" Row - Problem w/Averages

pover — Mon, 24 Jan 2011 22:12:58 GMT

Please report this as a bug.

But in the meantime, look at the attached workaround which has 2 parts.

Calculated dimension (Change the 2 for the number of values you want to show)

=aggr(if(rank(sum(Atomic_Value),4,1)<=2,City,'Others'),City)

Modified Expression

avg(aggr(sum(Atomic_Value)/sum(Value2),City))

Regards.

Show "Others" Row - Problem w/Averages

Tue, 25 Jan 2011 00:56:37 GMT

All - I really appreciate everyones feedback on this. Karl good idea with a calculated dimension! I will use this approach for now and as you and others suggest report this as a bug for future releases.