1 Reply Latest reply on May 19, 2016 9:09 AM by Ben Young

    Level of Detail calculation not working

    ernesto debeaumarchais

      I have a question on level of detail reporting, and hoping someone can help me out.  See the attached workbook.

       

      Basically, this is the data

       

           

      DateLocationIncidentsCensusInjury
      1/1/2000A1100NO
      1/1/2000A1100NO
      1/1/2000B1200YES
      1/1/2000B1200

      YES

       

      There are two locations, and each has two records.  Each incident has a record, and there is also a census record on both, because I can’t arbitrarily assign the total location census to one record.  I handle that later in the workbook.  There is also an Injury dimension.

       

      I calculate a percentage of incidents as incidents/ {fixed [Location],[Date] : MAX([Census])}

       

      The issue I am having is when I filter on INJURY.  The total census of both units should always be the denominator.  The incidents/numerator should filter, but the denominator shouldn’t.

       

      So unfiltered you have

      Incidents: (A) 2 + (B) 2 = 4

      Census: (A) 100 + (B) 200 = 300

      Calc: 4/300*10 = 13.3%

       

      If I filter out the ‘No’ Injuries it should be

      Incidents: (B) 2

      Census: (A) 100 + (B) 200 = 300 (I want the total census of both units, even though I am filtering the numerator)

      Calc: 2/300*10 = 6.67%

       

      But what it actually does is

      Incidents: (B) 2

      Census: (B) 200

      Calc: 2/200*10 = 10.00%

       

      So the Fixed thing isn’t actually taking the total census.  It still filters out the entire unit.  Maybe I'm misunderstanding how the FIXED thing works...?

        • 1. Re: Level of Detail calculation not working
          Ben Young

          Hi Ernesto,

           

          You are so close! You got the level of detail correctly for the location. But, for the total census count to work you need another level of detail calculation. As it stands now, the filter removes the other values from the view, leaving you with just the max of the census that still remains.

           

          The way to work around the filter as well is to make a second level of detail calculation on your Census Max calculation using this syntax: { SUM([Census Max]) }. The LOD expression in this case, without any specified dimensions, returns the total value of the summed Census Max for the dataset, regardless of whatever filters you have on.

           

          Attached is a workbook. Hope this helps!

          1 of 1 people found this helpful