Summing up excluded values as Other

[VivenReddy]

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

[Arthur_Fong]

There you go:

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

[VivenReddy]

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

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

[sunny_talwar]

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

Expression

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

[VivenReddy]

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.

[sunny_talwar]

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?

[VivenReddy]

Hi Sunny

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

Thanks for your assistance.

[sunny_talwar]

How about script intervention?

[VivenReddy]

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.

[sunny_talwar · Accepted solution]

This is the closest I am able to get