Skip to main content
Question

KPI with Case When but non-mutually-excluding conditions

  • June 24, 2020
  • 3 replies
  • 11 views
.
Level 7

Hi everyone,

I need a KPI in a form:

if X then A

if Y then B

if (X or Y) then (A+B)

else null end;

As I want to use it in an olap as dimention (first column) where the other KPIs deliver values for A,B and A+B, it is is not the optimal solution to split it into two tables (looks not good).

It seems for me, it is not possible to use CASE WHEN as it uses ELSE after the first condition and automatically excludes the A+B.

Do you know if there is a solution?

Best,

Maria

3 replies

S
Level 3
  • sion.oppen
  • Level 3
  • June 24, 2020

Hi DrWindy, You can apply multiple conditions with CASE WHEN. Its not clear to me why you would have A for X and B for Y while also requiring A+B if either X or Y? Shouldnt this be a separate KPI? You can include both KPIs in the same OLAP table. Example:

CASE

WHEN X THEN A

WHEN Y THEN B

END

CASE WHEN X OR Y THEN A+B END

.
Level 7
  • .14600833
  • Author
  • Level 7
  • June 24, 2020

Well,

I want to see how many activities A,B and A+B were performed in different departments.

And as I want it in one OLAP :

Activity___ Dept1___ Dept2 ___Dept3

A ____ | _______| ________| _______|

B ____ | _______| ________| _______|

A + B _ | _______| ________| _______|

If i apply 2 KPIs I am getting two columns for that and this is not what I want.

Best,

Maria

S
Level 3
  • sion.oppen
  • Level 3
  • June 25, 2020
Do you mean that you want to show for each Department how often Activity A and Activity B took place? And also show how often either Activity A or B took place? Would that not be a case count on Activity A and B (if X then A, if Y then B), where the total row of the OLAP table would reflect A+B?
Community Code of ConductAccessibility statement