Skip to main content
Question

Count cases wtih/ without specific activity in OLAP

  • March 12, 2020
  • 1 reply
  • 19 views
.
Level 2
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

1 reply

J
Level 0
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
Community Code of ConductAccessibility statement