Rank() on Multiple Dimensions with Other

[turodionova]

Dear experts,

I need to build a pivot table table with constrained and grouped by fields BRAND and ITEM based on Value ranking:

- top 3 BRAND and "OTHER" in BRAND  

- top 3 ITEMS for BRAND and "OTHER" for OTHER BRAND

 Below is example of what want to achieve:

BRAND	ITEM	BRAND	Sum(Value)
AAA	aaa_2	AAA	9 440
	OTHER	AAA	124
	aaa_4	AAA	140
	aaa_3	AAA	121
BBB	bbb_2	BBB	1 150
	OTHER	BBB	135
	bbb_4	BBB	142
	bbb_5	BBB	92
CCC	ccc_2	CCC	910
	OTHER	CCC	157
	ccc_4	CCC	179
	ccc_3	CCC	136
OTHER	OTHER		48

I use expression for BRAND, that works as I need:

aggr(if(rank(sum(Total<BRAND>Value) ) < 4, BRAND, 'OTHER'), BRAND)

For ITEM I use the following expression:

aggr(if(rank(sum(Total<BRAND,ITEM>Value) ) < 4, ITEM, 'OTHER'), BRAND, ITEM)

In result I get this table.

BRAND	ITEM	Sum(Value)
AAA	aaa_2	9 440
	OTHER	124
	aaa_4	140
	aaa_3	121
BBB	bbb_2	1 150
	OTHER	135
	bbb_4	142
	bbb_5	92
CCC	ccc_2	910
	OTHER	157
	ccc_4	179
	ccc_3	136
OTHER	OTHER	6
	ddd_6	18
	eee_1	10
	ddd_5	8
	ddd_1	5
	hhh_1	1

Could you please give an advice how to change logic for OTHER and get the first table.

Would be grate to find a solution that allows to add many addition levels with the same approach: one row for "OTHER" for all levels. 

Please find attached qwf as an example. 

[Nicolae_Alecu]

Hello,

One solution is to create a new BRAND column in script based on your criteria, and this column should have values {'AAA','BBB','CCC','OTHER'}

Have a nice day 😄

[turodionova]

Hello,

Thank you for your solution!

I actually need to recalculate ranks on the fly using filters.

[marcus_sommer]

You may try to extend the dimension-calculation by including the rank within the results, like:

aggr(if(rank(sum(Total<BRAND>Value) ) < 4,
   dual(BRAND, rank(sum(Total<BRAND>Value)), dual('OTHER', 4)), BRAND)

aggr(if(rank(sum(Total<BRAND,ITEM>Value)) < 4,
   dual(ITEM, rank(sum(Total<BRAND,ITEM>Value))), dual('OTHER', 4)), BRAND, ITEM)

[turodionova]

Thank you! I really like your solution! 

With your expressions I get a table with top 4 Items within OTHER. 

It is not one row for "OTHER" for all levels, but it looks much better. Many thanks! 

[marcus_sommer]

You may try to combine both parts, maybe like:

if(
aggr(if(rank(sum(Total<BRAND>Value) ) < 4,
   dual(BRAND, rank(sum(Total<BRAND>Value)), dual('OTHER', 4)), BRAND) = 'OTHER',
   dual('OTHER', 4),

aggr(if(rank(sum(Total<BRAND,ITEM>Value)) < 4,
   dual(ITEM, rank(sum(Total<BRAND,ITEM>Value))), dual('OTHER', 4)), BRAND, ITEM))

[turodionova]

Just noticed that this method with dual doesn't work properly in the app - mixed ITEMS over BRAND in a weird way. 🤔

[marcus_sommer]

Calculated dimensions have their difficulties especially if there is more as a single one within an object and if there are further requirements like creating own partial results for certain dimension-values or realizing a special sorting and so on.

I'm not sure anymore how I solved similar cases because it was for ages but it was always very ugly and quite different to the various cases. If I remember the essential parts it was using dual() as well as adding the rank-results to the dimension-values, like: 1. AAA; 2. CCC, 3. BBB in which the included number will enforce a string-sorting. Since QV 12 there is also a sorting-feature within the aggr() but I have no productive use-case for it.

Further helpful could be not to calculate everything within the dimensions else also within the expressions and/or to combine several ones within a range-function (for using multiple sum() with different conditions which return ZERO or NULL for the most rows unless the wanted ones) and/or calculating some parts within variables, like: concat(distinct aggr(if(rank(sum())< n;Field), Field), ',') which is then taken as filter-list within the dimension/expression and another one which didn't takes < n else >= n which might be applied for OTHER which is added as a real (dummy) value to the dimension (maybe within a second parallel field).

In this regard it might be also useful to count the number of possible/excluded dimension-values and also to combine multiple calculated dimensions within a single one and a threshold might then be <= 3 * Count(distinct Item) and afterwards such combined string-result is divided with string-functions.

Somehow will be always a way but the above mentioned stuff is complex, ugly and maybe not performant enough in the end. Therefore take a look if it could be simplified by using several objects for the information and also if you couldn't use inbuilt features. In QV there were possibilities within a straight-table and bar-charts but not in pivot to define top/flop n against a measure and showing everything else as others but if I remember correctly it had some limitations to the number of dimensions and probably some more. But even it couldn't fulfill all requirements it shouldn't be discarded to soon.

[turodionova]

There are a lot of ideas to work through. )

Thank you!