2 Replies Latest reply: Oct 23, 2014 10:48 AM by Alysha Kubsh RSS

    Calculating % change - Year over year

      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

          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

            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.