Rank per group of dimension together with two other dimensions

[Raymzzz]

Hi,

I'm struggling to show the correct ranking value within a specific dimension while showing also two other dimensions. See image.

I've tried a lot of things but to no avail. The formula used for the column Rank in the table is 

=aggr(rank(count(RNB)), [Product Class])

 The formula used for the Column '# Items' is

=count(RNB)

What I want to achieve is this

Product Class	Volume Bucket	Maturity Bucket	# Items	Rank
EUR A	R1 : (0-25M)	R4 : (5Y+)	625	1
EUR A	R3 : (100M+)	R1 : (0-1Y)	475	2
EUR A	R2 : (25-100M)	R1 : (0-1Y)	431	3
EUR A	R1 : (0-25M)	R1 : (0-1Y)	336	4
EUR A	R2 : (25-100M)	R4 : (5Y+)	284	5
EUR A	R2 : (25-100M)	R2 : (1-2Y)	232	6
EUR A	R2 : (25-100M)	R3 : (2-5Y)	145	7
EUR A	R3 : (100M+)	R2 : (1-2Y)	86	8
EUR A	R1 : (0-25M)	R2 : (1-2Y)	84	9
EUR A	R1 : (0-25M)	R3 : (2-5Y)	77	10
EUR A	R3 : (100M+)	R4 : (5Y+)	50	11
EUR A	R3 : (100M+)	R3 : (2-5Y)	29	12
EUR B	R1 : (0-25M)	R4 : (15Y+)	449	1
EUR B	R1 : (0-25M)	R2 : (5-10Y)	411	2
EUR B	R1 : (0-25M)	R3 : (10-15Y)	275	3
EUR B	R2 : (25-100M)	R1 : (0-5Y)	254	4
EUR B	R1 : (0-25M)	R1 : (0-5Y)	204	5
EUR B	R2 : (25-100M)	R2 : (5-10Y)	142	6
EUR B	R3 : (100M+)	R1 : (0-5Y)	105	7
EUR B	R2 : (25-100M)	R3 : (10-15Y)	104	8
EUR B	R2 : (25-100M)	R4 : (15Y+)	64	9
EUR B	R3 : (100M+)	R2 : (5-10Y)	26	10
EUR B	R3 : (100M+)	R3 : (10-15Y)	5	11
EUR B	R3 : (100M+)	R4 : (15Y+)	1	12
EUR C	-	R1 : (0-6M)	3870	1
EUR C	-	R2 : (6-12M)	1661	2
EUR C	-	R3 : (12-18M)	1236	3
EUR C	-	R4 : (18M+)	396	4
EUR D	-	R1 : (0-6M)	241	1
EUR D	-	R2 : (6-12M)	183	2
EUR D	-	R3 : (12-18M)	7	3

I would be grateful if anybody has an idea how to solve this or why it is behaving as shown in the image above.

Many thank in advance.

[Chanty4u]

Try this 

=Aggr(

    Rank(

        Count(RNB), 1 

    ),

    [Product Class],

    [Volume Bucket],

    [Maturity Bucket]

)

Or 

=Aggr(

    Rank(

        Count(RNB)

    ),

    [Product Class],

    [Volume Bucket],

    [Maturity Bucket]

)

[Raymzzz]

Thanks, but no exactly what I want. Both formulas give this result :

[marcus_sommer]

You may try something like:

=aggr(NODISTINCT rank(count(RNB)), [Product Class])

[Raymzzz]

Thanks for your reaction Marcus, but result is not what I expect :

[marcus_sommer]

The second dimension needs to be also added to the aggr(), like:

=aggr(NODISTINCT rank(count(RNB)), [Product Class], [Volume Bucket])

[Raymzzz]

Not yet 😞 

[marcus_sommer]

For me it's working and even a normal rank() without an aggr() returned the expected results. Are all dimensions native fields or are they calculated dimensions (should be avoided if any possible)?

[Raymzzz]

Thanks, but I see only two dimensions in your example ?  I'm struggling with three or more dimensions per row. Thanks.

[marcus_sommer]

I think the rank-calculation worked it the same way also with more dimensions.

Therefore I assume that you aren't struggling the rank-results else with the (asc/desc) sorting within the view. If so it becomes difficult because it's a multi-dimensional sorting and the mix of dimensions and expression-results conflicts with each other respectively with the processing-order which doesn't support a n layer evaluation.

There are some workarounds available for such scenarios depending on the data-set and the object. One of them would be not to use a native field as dimension else a calculated dimension which includes the expressions-results. In your case the second dimension may look like:

aggr(dual([Volume Bucket], count(RNB)), [Product Class], [Volume Bucket])

and then sorting this dimension against it's numeric value.