Developing KPI based data 3 months back

john_Talampas — Sat, 16 Nov 2024 20:04:48 GMT

Hello,

I'm trying to develop 2 KPI measures that users can track the percentage over a time (that a user selects):

1. KPI Volume %: # Distinct Accounts Discharged + Denied (3 months prior) / #Distinct Accounts Discharged (3 months prior)

2. KPI Amount %: Denied Amount (3 months prior) /Total Charges (3 months prior)

I'm having issues and was hoping someone could point me in the right direction.

For example; say a user selected the dates between 2018-07 and 2019-02. Here is a small sample of what the raw data would look like (see attached):

The data attached looks at a few ACCOUNTS in my data set. An account can have a denial attached, indicated by the Denial_ID (if it is blank then no denial occurred).

The 2 dates to be aware of is "DISCHARGE_DATE" and "DENIAL_DATE" (when it was denied).

The request is look at a given date range, that the user selects, and to then look back 3 months prior to determine:

1. The number of distinct Accounts that were Discharged + Denied attached (numerator)

2. The number of distinct Accounts that were discharged (denominator)

This will give me a % that will become my KPI (see attached).

What method would be easiest to implement in order to calculate the 2 KPI's I'm after (3 months prior). I'm playing around with Set Analysis and Flags within my load script but to no avail.

John

Re: Developing KPI based data 3 months back

DavidM — Mon, 16 Sep 2019 07:01:48 GMT

Hi John,

flags and set analysis is the right way to go. The way I would do it is like this:

1. Create master calendar, link denial & discharge date to master calendar.

2. Create monthly flag like this: Year*12 + Month = for 2019-02 this would be 24230. Now you have unique number for each month in any year which is ordered and use it in set analysis.

3. You will have to use three conditions in set analysis - Max(Flag), Max(Flag)-1 and Max(Flag)-2. This way it will always calculate 3 months back from selected month, or last 3 months if no selection.

I know this is very top level description on how to do this, but I hope this helps you

topic Developing KPI based data 3 months back in App Development

Developing KPI based data 3 months back

Re: Developing KPI based data 3 months back