Calculating % change - Year over year

[#1]

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.

[Gysbert_Wassenaar]

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)

[#3 · Accepted solution]

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.