Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

set analysis for trending by rolling years

Hi All,

I'm working on creating a trend chart (trending by rolling years) of $ per member per year for patients with claims data.

I have the following dimensions:

Rolling Year End Date= EndDt

Payment Category= PaymentCat

I'm struggling to get my expression to work for all scenarios:

PMPY $=(SUM({<EndDt>}pay_adj_amt)/SUM({<EndDt>}MemberMonths))*12

Currently, the above expression works if I only have 1 patient selected. If I have more than 1 patient selected, it appears as if the member month aggregation only counts member months for patients in the specific payment category, versus member months for all patients selected. How should I adjust the set analysis to account for this?


***See test QVW attached and see comment below for further explanation

4 Replies

Re: set analysis for trending by rolling years

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Or this blog post: The As-Of Table

talk is cheap, supply exceeds demand
Not applicable

Re: set analysis for trending by rolling years

Can you please post the sample qvw with test data  ?

New Contributor III

Re: set analysis for trending by rolling years

Thanks for the resources! One thing I probably should have noted in my question is that my dashboard is already in the rolling year structure. Many of the metrics displayed in the dashboard are aggregated by rolling year prior to being pulled into QlikView. When a user opens the dashboard, the default EndDt is the most recent rolling year of data available - let's say rolling year ending 12/31/2015. The earliest rolling year available has a rolling year end date of 12/31/2013. I'd like to be able to show trends from 12/31/2013 to the most recent rolling year - 12/31/2015.

The structure of the data I am using has the rolling year end date, a payment category (inpatient, outpatient, etc.) , payment amount, member months for the patient, etc.

New Contributor III

Re: set analysis for trending by rolling years

Below is a sample QVW with 2 "dummy" patients. I've included the bar chart for the EndDt selected and the issue I'm seeing is that the trend chart doesn't show the correct PMPY $ if both patients don't have claims in the payment category during the rolling year. For example, only one patient has SNF claims in rolling year ending 2015-12-31. Payments for that patient's SNF claims total $15000. The bar chart shows correctly that the PMPY $ for SNF is $7500 (because there are 2 patients and both have 12 member months). However, the trend chart shows $15000 for SNF for that time frame... I don't think it is taking into account that there is another patient with 12 member months that should be included in the denominator.