Dear community,
I would like to create an OLAP table which counts cases with/ without specific activities.
In this case, Id like to count cases which through both Confirm Reservation and Sell Ticket.
tickets_log524644 11.8 KB
Id 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 dont use wildcard like "Confirm Reservation"
If I could, Id like to show the result as pie chart.
Thank you for your support.
Sugano
Page 1 / 1
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, thatll solve your issue!
Best regards,
Justin
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, thatll solve your issue!
Best regards,
Justin
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.