Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
It might be helpful to look at a slightly bigger sample data and the expected number you expect to see out from it
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
Thanks so much Marcus. It works!