Hi Silvakumar,
I believe this is a use case focused on checking whether the claim is getting paid after a denial.
If I understand correctly, you want to count paid activities after Claim Denial, being Payment Date at least 60 days after the Claim Denial.
On a case level, it should be:
PU_COUNT(
CASETABLE, ACTIVITYTABLE.EVENTTIME,
DAYS_BETWEEN( PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Claim Denial'), ACTIVITYTABLE.EVENTTIME) >= 60 AND ACTIVITYTABLE.ACTIVITY = 'Payment'
)
I`m considering you have an activity called 'Payment'. If you don't have it, replace it with a similar column/logic to identify a payment in the activity table.
If you want to have a single KPI, just wrap same formula within a SUM()
Best,
Gabriel
In case this information is displayed within the "regular" data it could also be sufficient to simply use CASE WHEN table1.field1 > table2.field2 THEN ...ELSE ... END
Best,
Kevin
Hi Silvakumar,
I believe this is a use case focused on checking whether the claim is getting paid after a denial.
If I understand correctly, you want to count paid activities after Claim Denial, being Payment Date at least 60 days after the Claim Denial.
On a case level, it should be:
PU_COUNT(
CASETABLE, ACTIVITYTABLE.EVENTTIME,
DAYS_BETWEEN( PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Claim Denial'), ACTIVITYTABLE.EVENTTIME) >= 60 AND ACTIVITYTABLE.ACTIVITY = 'Payment'
)
I`m considering you have an activity called 'Payment'. If you don't have it, replace it with a similar column/logic to identify a payment in the activity table.
If you want to have a single KPI, just wrap same formula within a SUM()
Best,
Gabriel
I want get the each case in how many days it got paid. with this formula PU_COUNT(
CASETABLE, ACTIVITYTABLE.EVENTTIME,
DAYS_BETWEEN( PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Claim Denial'), ACTIVITYTABLE.EVENTTIME) >= 60 AND ACTIVITYTABLE.ACTIVITY = 'Payment'
) i have payment activities (payemntA, PaymentB, Payment C etc), I'm not able fix this is my activity. days_between first claim denial date to first payment activity date but that first payment activity is after the claim denial.
Hi @sivakumar.sivap14
I would approach this differently. I would write a CASE WHEN PU_FIRST(CASE_TABLE, ACTIVITY_TABLE.ACTIVITY, ACTIVITY LIKE 'Payment%') > PU_FIRST(CASE_TABLE, ACTIVITY_TABLE.ACTIVITY, ACTIVITY LIKE 'Denial%') THEN CALC_THROUGHPUT(...) ELSE NULL END
Check if this works
Hi Sivakumar,
"each case in how many days it got paid." this should be fine if you know exactly which Payment activity you want to compare with (as you mentioned, you could have multiple payments after the denial). Is it against the first payment that occurs after claim denial? Is it the average time of the payments that occur after the claim denial or another specific logic?
Getting the days between 2 activities can be achieved with the CALC_THROUGHPUT() formula, but you first need to establish
the right logic.
Example:
Case No. 123456789
Payment A- 01/01/2021
Denial- 02/01/2021
Payment B-03/01/2021
Payment C - 04/01/2021
Payment D - 05/01/2021
What would be the "how many days it got paid"? (i.e. Is it from the denial to the last Payment, an average of these values or to the first?)
Denial to first payment which is Payment B-03/01/2021 for this case
In this case you can leverage SOURCE()- TARGET() and set source as denial and Payment as TARGET()
Check out this documentation: https://confluence.celonis.com/display/PQLdevelopment/SOURCE+-+TARGET
Source and target only work if the very next activity after Denial is the Payment (it also generates an error in case you are trying to combine with other calculations that are not included on SOURCE/TARGET).
Thanks Sivakumar, the following formula will do the work
DAYS_BETWEEN(
PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Claim Denial'),
PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Payment' AND
DAYS_BETWEEN (PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Claim Denial'),
PU_FIRST(CASETABLE,ACTIVITYTABLE.EVENTTIME,ACTIVITYTABLE.ACTIVITY = 'Payment'))>=60)
)
