3 Replies Latest reply on Nov 22, 2018 8:34 AM by Paul Wachtler

    Exclude LOD before Dimension filter

    Maddy Peña

      Hello all!

      I have a database with headcount data and turnover data from my company.

      I can calculate the turnover with no problem (turnvoer/(avg headcount)), in this case, by week; but when I want to analyze a particular turnover reason (i.e voluntary, involuntary), my headcount is zero.


      I know why this happens, as an active employee doesn't have a turnover reason, so it's null. But I cannot calculate, even with LOD calculation, the headcount excluding the turnover reason, because, as I understand, only FIXED is calculated before dimension filters:


      (null action reason is included in other)





      Is there a way to achieve this?

      Can be even a problem of database structure?


      Thanks in advance.

        • 1. Re: Exclude LOD before Dimension filter
          Paul Wachtler

          Hi Maddy,


          You're correct that only FIXED LODs can be evaluated before dimension filters.  EXCLUDE and INCLUDE are evaluated after.


          To that end, you don't need an EXCLUDE LOD, but rather a FIXED LOD.  This calculation will keep the Headcount Total static, regardless of the turnover reason you select with your filter:

          {FIXED [Tenure], [Calendar day]: SUM([Headcount])/[SelectedWeeks]}


          Let me know if you have any questions.




          • 2. Re: Exclude LOD before Dimension filter
            Maddy Peña

            Hi Paul!

            Thank you so much for your answer!

            So for instance, if I have multiple graphs with different dimensions, would I need to create a "headcount" calculation for each one or you recommend to add those possible dimensions to the LOD fixed calculation?


            i.e I have a turnover % graph per age range, site, and so on:

            {FIXED [Tenure], [Age range],[site], [Calendar day]: SUM([Headcount])/[SelectedWeeks]}

            • 3. Re: Exclude LOD before Dimension filter
              Paul Wachtler

              If you would be ok with have a parameter that lets you select either all of the reason codes or only one at a time, we could figure out a calculation that would let you get the headcount without having to create a new calculation for each view.  So instead of being able to select two categories at a time it'd either be all or just one.


              If you need to keep your filter to be able to select two reason codes at once, then yea the only way I can think of making it work is to create a new calculation for each view's level of detail.