topic Calculating % change - Year over year in QlikView

Calculating % change - Year over year

Tue, 21 Oct 2014 15:44:30 GMT

Hello,

I am trying to calculate the % change of allowed dollars PMPM between years. I am currently using the following formula:

(above( sum([AMT_ALLOWED])/ sum({1}MM_UNITS))/( sum([AMT_ALLOWED])/ sum({1}MM_UNITS))) -1

Note - i need the "/ sum({1}MM_UNITS))" to stay in the expression to get an accurate PMPM. Because if someone wants to restrict by a claim parameter, I still need my underlying population to stay constant (because i need members for PMPM, not claimants).

However, if I make any selection to restrict my data, I encounter the following problem (Say I only want to see 'Owners' and 'PR Individual')--

Before I made the "%Change Allowed" field, anything that was excluded from my selection would collapse. Now everything remains and is populated with nulls/zeros. Any suggestions on how to change my formula above to get the excluded fields to collapse again? Or perhaps a better way to calculate % change between years as shown above?

Thank you.

Re: Calculating % change - Year over year

Gysbert_Wassenaar — Tue, 21 Oct 2014 19:47:38 GMT

Perhaps the problem is that you're dividing by zero for the other benchmark values because of the {1}. Maybe this works: alt((above( sum([AMT_ALLOWED])/ sum({1}MM_UNITS))/( sum([AMT_ALLOWED])/ sum({1}MM_UNITS))) -1,0)

Re: Calculating % change - Year over year

Thu, 23 Oct 2014 14:48:40 GMT

Thanks Gysbert - that didn't work for this scenario, but i found a way to simplify a few of my other expressions and reference them.

Changed my expression for 'MMs' to:

sum({1<BENCHMARKS=P(BENCHMARKS),INCURRED_YEAR=P(INCURRED_YEAR),INCURRED_YEAR_AND_MONTH=P(INCURRED_YEAR_AND_MONTH)>} MM_UNITS)

'Total Alwd PMPM' to: sum([AMT_ALLOWED])/[MM's]

'%Change Alwd' to: ([Total Alwd PMPM])/ below([Total Alwd PMPM])-1

This way i was able to avoid using the {1} in my '% change' column altogether, by using my set analysis on the front end in my MM's expression.