Count cases wtih/ without specific activity in OLAP

Dear community,

I would like to create an OLAP table which counts cases with/ without specific activities.
In this case, I’d like to count cases which through both “Confirm Reservation” and “Sell Ticket”.

I’d like to get the result like this.
Confirm Reservation (Booking Number) : 1
Confirm Reservation (QR Code) : 2
Else : 2

*D-004 is treated as “Else” because D-004 passed though only “Confirm Reservation”
The name of activity is different from my raw data, so please don’t use wildcard like "Confirm Reservation"

If I could, I’d like to show the result as pie chart.

Thank you for your support.
Sugano

Hi @Sugano,

This is most efficiently solved by using a CASE WHEN Statement combined with PU_COUNT Function. To visualize it in a Pie Chart, you have to set the first statement as the dimension and the second one as KPI. You could apply it as follows:

First Statement - Dimension Definition:

CASE
WHEN PU_COUNT ( “CASE TABLE”, “ACTIVITY TABLE”.“ACTIVITY COLUMN”, “ACTIVITY TABLE”.“ACTIVITY COLUMN” IN (‘Confirm Reservation (Booking Number)’)) > 0
AND PU_COUNT ( “CASE TABLE”, “ACTIVITY TABLE”.“ACTIVITY COLUMN”, “ACTIVITY TABLE”.“ACTIVITY COLUMN” IN ( ‘Sell Ticket’ )) > 0
THEN ‘Confirm Reservation (Booking Number)’
WHEN PU_COUNT ( “CASE TABLE”, “ACTIVITY TABLE”.“ACTIVITY COLUMN”, “ACTIVITY TABLE”.“ACTIVITY COLUMN” IN (‘Confirm Reservation (QR Code)’)) > 0
AND PU_COUNT ( “CASE TABLE”, “ACTIVITY TABLE”.“ACTIVITY COLUMN”, “ACTIVITY TABLE”.“ACTIVITY COLUMN” IN ( ‘Sell Ticket’ )) > 0
THEN ‘Confirm Reservation (QR Code)’
ELSE ‘ELSE’
END

Second Statement - KPI Definition:

COUNT_TABLE(“CASE TABLE”)

Hopefully, that’ll solve your issue!

Best regards,

Justin