Limiting Rows By Set Expression

[Anonymous]

Trying to limit the number of rows to 10 in a straight chart with two dimension and three expressions.

Dimensions:                                          

Client    

Product

Expressions:

Sum of Sales for Current Year

Sum of Sales for Previous Year

Difference of above two

Need to limit to top 10 "Difference of above two" through Set Expression and not through Dimension Limits.

Dimensions:

=if(Aggr(Rank(sum({<Year={$(vCurrYear)}>} Sales) - sum({<Year={$(vCurrYear)}>} Sales)), Client, Product) <= 10, Client)

and

=if(Aggr(Rank(sum({<Year={$(vCurrYear)}>} Sales) - sum({<Year={$(vCurrYear)}>} Sales)), Client, Product) <= 10, Product)

Expressions:

=(Sum({<Year={$(vCurrYear)}, Client={"=Rank(sum({<Year={$(vCurrYear)}>} Sales) -

              sum({<Year={$(=vCurrYear-1)}>} Sales)) <=10"}>} Sales))

=(Sum({<Year={$(=vCurrYear-1)}, Client={"=Rank(sum({<Year={$(vCurrYear)}>} Sales) -

              sum({<Year={$(=vCurrYear-1)}>} Sales)) <=10"}>} Sales))

But can't make it work..

[Gysbert_Wassenaar]

1. If you set the option Max Number (1-100) to 10 on the Presentation tab does that give you the result you need?

2. Top 10 of what value? Sales of Current Year?

3. Please post an example document

[Anonymous]

Attached is a sample file.

Max Number option doesn't help in a sense because '%' column Total then doesn't show desired total.

Top 10 based of 'Diff' column.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw