Double Aggregate rank

[Anonymous]

Hi guys,

I have a table containing two KPIs that I need to aggregate twice and sum the result. I want to do this in a textbox expression.

Category1	Category2	KPI1	KPI2
A	B	10	20
C	D	20	500

For this, I have to aggregate once according to a rank that identifies the worst performers (largest values) for KPI1 and secondly for the worst performers of KPI2.

The two steps:

- Reduce the table down to the worst 40% of KPI1

- Reduce this down to the worst 50% of KPI2.

I started out with just limiting the table to the worst 40% of KPI1 which worked with the following expression:

sum(aggr(if(rank(TOTAL KPI1) <= NoOfRows(TOTAL)*0.4,KPI1),Category1, Category2))

Now I would need a second aggregation that again ranks this first aggr() according to KPI2.

I used this expression but it doesn't return the right value.

sum(aggr(if(rank(aggr(if(rank(TOTAL KPI1) <= NoOfRows(TOTAL)*0.4,KPI2),Category 1, Category 2) <= NoOfRows(TOTAL)*0.5, KPI2), Category 1, Category 2))

How can I solve this?

[sunny_talwar]

It might be helpful to look at a slightly bigger sample data and the expected number you expect to see out from it

[marcus_sommer · Accepted solution]

I'm not absolutely sure if I had understood your question right but I think even if not the following would be helpful for you.

load * inline [

C1, C2, K1, K2

a, b, 1, 1

c, d, 2, 2

e, f, 3, 4

g, h, 4, 8

i, j, 5, 16

k, l, 6, 32

m, n, 7, 64

o, p, 8, 128

q, r, 9, 256

s, t, 10, 1024

];

=  sum(aggr(if(

    rank(total

    sum(aggr(if(rank(TOTAL sum(K1)) <= NoOfRows(TOTAL)*0.4,K1),C1, C2)))

    <= NoOfRows(TOTAL)*0.4*0.5, K2),

    C1, C2))

- Marcus

[Anonymous]

Thanks so much Marcus. It works!