topic Limiting Rows By Set Expression in QlikView

Limiting Rows By Set Expression

Anonymous — Thu, 31 Oct 2013 23:47:38 GMT

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 — Fri, 01 Nov 2013 07:39:14 GMT

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 — Fri, 01 Nov 2013 21:57:08 GMT

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 — Sat, 02 Nov 2013 09:45:42 GMT

See attached qvw