How to Calculate a KPI based on different rows?

Question & Answer
, ,
1

I’m polling the Connect APIs and persisting in an Aurora DB table where the relevant data looks like this:

DateTime              QueueId    KPI                  Value
-------------------   --------   ------------------   ----------
2023-08-30 12:34:56   QueFoo     CONTACTS_ABANDONED   10
2023-08-30 12:34:56   QueFoo     CONTACTS_QUEUED      50

I can display and aggregate either individual KPI correctly, but I would like to calculate a KPI called Abandon Rate that is CONTACTS_ABANDONED / CONTACTS_QUEUED. Is this possible?

Maybe I should rethink by schema and put all KPIs in one row?

Thanks,
– Jeff

2

@JeffGilbert

You can try lag function.

3

Try
sumIf(Value, KPI='CONTACTS_ABANDONED')/sumIf(Value, KPI='CONTACTS_QUEUED')
?