Hi Everyone!
I'd like to ask your inputs on a report I'm trying to create based on some criteria. I need to create a top 5 straight table and an "others" category for the rest. I am currently using a calculated dimension and works while using 2 dimension. But it becomes a problem once I added a 3rd dimension. Below is a sample of the expected output:
| Product Name | Category | Type | sum(Value) |
| Product 1 | C1 | T1 | 1000 |
| Product 2 | C1 | T2 | 800 |
| Product 3 | C1 | T3 | 700 |
| Product 4 | C2 | T4 | 300 |
| Product 5 | C1 | T1 | 200 |
| Others | <blank> | <blank> | 3000 |
| Calculated dimension | expression | status |
| Product Name | agg(if(rank(Sum(Value))<6,[Product Name],'Others'),[Product Name]) | shows expected results |
| Category | agg(if(rank(Sum(Value))<6,Category,''),[Product Name],Category) | shows expected results |
| Type | - agg(if(rank(Sum(Value))<6,Type,''),[Product Name], Category, Type)
- agg(if(rank(Sum(Value))<6,Type,''),[Product Name],Type)
| does not show expected results 1st expression shows all records outside of top 5 2nd expression shows top5 5 but null values in dimension Type |
The ranking is based only for the Product Name, wherein multiple entries can be found due to different value combinations of Category and Type. But I need also to show the corresponding Category and Type values.
Any inputs would be greatly appreciated.
