Skip to main content
Question

How do I only consider the last activity for repeated activities within a RUNNING_TOTAL statement?

  • May 5, 2022
  • 1 reply
  • 6 views
A
Level 2

Hi everyone,

 

to show a graph with how many goods were not sent to customers (yet) on a specific date I followed the instructions of this example for filling ranges (Link).

 

Dimension:

RANGE_APPEND(ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"), '7D', {d '2021-01-01' } , TODAY ())

 

KPI:

RUNNING_TOTAL(SUM(

CASE 

WHEN "_CEL_O2C_ACTIVITIES"."ACTIVITY_DE" = 'X'

THEN 1 

WHEN "_CEL_O2C_ACTIVITIES"."ACTIVITY_DE" = 'Y'

THEN -1

ELSE NULL END))

 

However, goods may be sent or received in multiple deliveries, thus leading to repeated activities.

 

1st Question: How do I only consider the last activity (and respective timestamp) per case?

 

2nd Question: How do I properly include this additional check, ensuring that all goods are actually sent out?

 

(CASE WHEN PU_FIRST("VBAP","MSKA"."VBELN" || "MSKA"."POSNR","MSKA"."KALAB" > 0 OR "MSKA"."KASPE" > 0) = "VBAP"."VBELN" || "VBAP"."POSNR" 

THEN 1 ELSE 0 END) =0

 

Thanks and best regards

Aram

 

    1 reply

    erik.zwick12
    Celonaut
    Hey Aram, To address the two questions: 1.) Have you tried using a PU_LAST to pull up the last timestamp for a case? You can then access the last activity as well with that respective timestamp 2.) You should be able to use that formula in your original case when KPI statement by adding an AND and then that statement. For Example: CASE WHEN "_CEL_O2C_ACTIVITIES"."ACTIVITY_DE" = 'X' AND PU_FIRST("VBAP","MSKA"."VBELN" || "MSKA"."POSNR","MSKA"."KALAB" > 0 OR "MSKA"."KASPE" > 0) = "VBAP"."VBELN" || "VBAP"."POSNR" THEN 1 ... Let us know if this helps.
    Community Code of ConductAccessibility statement