Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
wzsc
Contributor
Contributor

Ranking with more than 2 dimensions

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 NameCategoryTypesum(Value)
Product 1C1T11000
Product 2C1T2800
Product 3C1T3700
Product 4C2T4300
Product 5C1T1200
Others<blank><blank>3000

 

Calculated dimensionexpressionstatus
Product Nameagg(if(rank(Sum(Value))<6,[Product Name],'Others'),[Product Name])shows expected results
Categoryagg(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.

0 Replies