Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Summing up excluded values as Other

I have attached a sample qvw with some fruit sales. It has a list box and a straight table showing sales of each fruit.

If no fruit have been selected from the list box then sales of all fruit should be displayed in the straight table. When fruit selections have been made then those selected fruit should be in the straight table AS WELL AS combining all other fruit sales (excluded fruit) into 'Other'. 

 

1 Solution

Accepted Solutions

Re: Summing up excluded values as Other

This is the closest I am able to get

image.png

11 Replies
Partner
Partner

Re: Summing up excluded values as Other

There you go:

Fruits.PNG

You would need another dimension to store the 'Others' in it.

Refer attached qvw for the steps to create the new dimension named 'Fruits'.

Thanks and regards,

Arthur Fong

Partner
Partner

Re: Summing up excluded values as Other

Thank you Arthur. Your solution gives the correct end result.

Is there any way this can be done without changing the script?

Re: Summing up excluded values as Other

This probably does that job, but not the best of ways to do it...

Step 1: Use Dimension Limits tab to show Only Largest =Count(DISTINCT Fruit)+1

Step2: Enable Show Others under Options on Dimension Limits tab

image.png

Expression

Dual(Sum({1}Total), RangeSum(Sum(Total) * 1E10, Sum({<Fruit = e()>}Total)))
Partner
Partner

Re: Summing up excluded values as Other

Great Solution Sunny.

Here's another curveball. What if I had to convert this to a pivot table?

1. Add Category and Supplier dimensions

2. Pivot on Supplier

3. 'Other' sales still required for Fruit

4. 'Other' sales required for Supplier

Updated qvw attached.

Re: Summing up excluded values as Other

What is the expected output? Can you provide the numbers in an Excel file? Also, you want 0 intervention in the script? or are you okay with an Island Table which have a minimum impact on the other tables in your data model?

Partner
Partner

Re: Summing up excluded values as Other

Hi Sunny

I've tried to quickly draft the excel output. Not sure how accurate my numbers are though.

Thanks for your assistance.

Re: Summing up excluded values as Other

How about script intervention?

Partner
Partner

Re: Summing up excluded values as Other

First prize would be without script changes - not sure if thats possible.

In this simple fruit data sample it might be easy to get the script changed. I would be concerned how to apply those sorts of changes to my 'complicated' datamodel.

Re: Summing up excluded values as Other

This is the closest I am able to get

image.png