Hi Everyone,
I am developing an app for a project that needs to use ranking in a straight table. The users want to show the top 5 products and an 'others' to show the rest. I tried to use first the dimension limitation available in qlik sense, but it usually shows more values and/or more dimensions than intended even if I have specified the rank function to return only 1 rank per row. so I used instead calculated dimensions.
Below is a sample what I wanted to return. I am able to correctly show the 1st and 2nd dimension(Product Name and Category) but I cannot return my intended values for the 3rd dimension(type). the 3rd dimension either returns only blanks, nulls, or shows other Product's not included in top 5. Using my example below, Products can have entries with different Category and/or different Type.
| Product Name | Category | Type | Value |
| Product 1 | C1 | T1 | 1000 |
| Product 2 | C1 | T2 | 800 |
| Product 3 | C3 | T3 | 700 |
| Product 4 | C2 | T4 | 300 |
| Product 5 | C1 | T1 | 200 |
| Others | <blank> | <blank> | 3000 |
Below are the calculated dimensions I am using
| Calculated Dimensions | Expression | Status |
| Product Name | aggr(if(rank(sum(Value)<6, [Product Name],'Others'),[Product Name]) | expected values returned |
| Category | aggr(if(rank(sum(Value)<6, [Category],''),[Product Name]) | expected values returned |
| Type | aggr(if(rank(sum(Value)<6, [Type],''),[Product Name]) | expected values NOT returned |
Greatly appreciate it for any inputs!
Thanks, wsc
