Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 😄
Hello,
Thank you for your solution!
I actually need to recalculate ranks on the fly using filters.
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)
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!
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))
Just noticed that this method with dual doesn't work properly in the app - mixed ITEMS over BRAND in a weird way. 🤔
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.
There are a lot of ideas to work through. )
Thank you!