3 Replies Latest reply on Jan 11, 2018 3:09 PM by Wesley Magee

    Error summing LOD function

    Dakota Snyder

      Hi there,

       

      I've hit a wall with calculations involving level of detail.

       

      In the attached workbook, I'm trying to figure out the client survey participation percentage for each office. The *2017 prioritized participation status takes into account clients that have been surveyed more than once in a year. For Office A, 7 is the correct number. However, I am getting an answer as 9 when I use the below calculation.

       

      IF [*2017 Prioritized participation status] = "1. Surveyed" THEN 1 ELSE 0 END

       

      and then I sum it via: SUM([*2017 # surveyed]) - I'm getting 9.

       

      There are 7 surveyed statuses and 2 removed (sheet 3, breaks out the correct and incorrect numbers). I'm confused as to why the 2 removed statuses are being summed in the above equation.

       

      My end goal is to create an equation that looks like:

           (# surveyed + # interviewed + (#scheduled * .5))/(Total # of statuses)

       

      Could anyone assist? Thanks so much!!

        • 1. Re: Error summing LOD function
          Wesley Magee

          Dakota,

           

          Let me know if I've miss understood the problem, but it looks like it's just the type of aggregation your using that isn't working properly.

           

          For the 9 that you're showing as correct, you're taking the distinct count of Client ID, but for the "*2017 # surveyed" calculation you're taking a strict row count. To fix this you'll just need to make a small change to your "*2017 # surveyed" calc where you replace the 1 or 0 option with the Client ID. It would look like this:

           

          IF [*2017 Prioritized participation status] = "1. Surveyed" THEN [Client ID] END

           

          Just take the COUNTD of this calculation to get the correct result. You could also pre-aggregate this equation to make it a little faster to make adjustment. Then it would just look like this:

           

          COUNTD(IF [*2017 Prioritized participation status] = "1. Surveyed" THEN [Client ID] END)

          • 2. Re: Error summing LOD function
            Dakota Snyder

            Thanks so much Wesley, that totally worked. I think I got a little lost in the weeds and ignored the basics!

             

            Cheers,

            Dakota

            • 3. Re: Error summing LOD function
              Wesley Magee

              That happens to me all of the time!

              Cheers