new KPI

[Manni_SM]

Hi All,

@Vegar 

i want to build new KPI with below logic.

Add Billing TurnAroundTime KPI  ( EcounterDate---> billable timestamp)

create a new   KPI that calucalte the Median turnaround time between

EcounterDate and

billable timestamp/ final timestamp

so how to implement this?

[hugo_andrade]

Hi @Manni_SM !

You can do this by creating a measure that calculates the time difference between the encounter date and the billing (or final) timestamp for each record, then taking the median of those differences.

First thing is to make sure you have one “billing timestamp” per encounter (or whatever your grain is). If you have multiple billing events per encounter, decide whether “billable timestamp” means the earliest, latest, or the final one. Median only makes sense once the grain is consistent.

High-level approach

1. Create a duration per record
   Duration = (BillableOrFinalTimestamp - EncounterDate) expressed in minutes/hours/days.

2. Create a KPI measure using Median(Duration)
   In Qlik, the KPI can use Median() directly over that duration expression.

3. Handle nulls and bad data
   Exclude rows where either timestamp is missing, and exclude negative durations (billing before encounter) unless you explicitly want them.

Example KPI measure (days)

Median(
If(
Len([EncounterDate])>0 and Len([BillableTimestamp])>0,
([BillableTimestamp] - [EncounterDate])
)
)

If you want billable timestamp OR final timestamp (use final when billable is empty)

Median(
If(
Len([EncounterDate])>0 and (Len([BillableTimestamp])>0 or Len([FinalTimestamp])>0),
( Alt([BillableTimestamp],[FinalTimestamp]) - [EncounterDate] )
)
)

Notes

• If your timestamps are true Qlik dual date-time fields, subtraction returns days (fractional). Multiply by 24 for hours, by 24*60 for minutes.

• If you need “per encounter” median (one value per EncounterID), then you should first aggregate to one duration per EncounterID (for example using Aggr()) and then take the median of those encounter-level durations.

If you tell me what your unique key is (EncounterID?), and whether there can be multiple billable/final timestamps per encounter, I can point you to the cleanest expression pattern for your exact data shape.