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
