7 Replies Latest reply: Feb 17, 2015 11:02 AM by Melissa van Kluyve RSS

    Set analysis conflict

      Hi all,

       

      I am trying to predict the total headcount of a Business over the next few years. To do this we've segmented the workforce and said that some people are going to be affected by Revenue Growth while others will be affected by Digital Revenue.

       

      As part of this we are also assuming that places like HR will be affected by the headcount in the rest of the business (not including themselves).

       

      To calculate the Headcount changes in HR we have to calculate the headcount Growth Rate in the rest of the Business. This has left me with a variable where I sum everything where {<[DemandDriver] - = {'Headcount'}>}. This variable works on it's own and gives me the correct value. The variable is called vNoHRGrowthRate

       

      So to calculate the headcount Growth in HR I end up with this formula:

      =Sum({<DemandDriver={'Headcount'}>}[HeadcountSum]*
      pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex))

       

      Where [cHeadcount] is a field where the is value dependent on the particular [HeadcountSum] is being Summed.

      [YearIndex] is a coded inline table where 2015=0, 2016=1 etc.

       

      The problem is that the set analysis in Red is clashing with the set analysis in the vNoHRGrowthRate variable and is returning a null value. (The section in Red says Sum everything where it IS equal to headcount and in the variable it additionally tells it to sum everything where DemandDriver is NOT equal to headcount)

       

      If I replace the variable with its numerical value then the formula works - i can't leave it like this though as the values change dynamically.

       

      I just need to put in some notation that tells it to ignore the previous set analysis before calculating the value of vNoHRGrowthRate.

       

      Is there a way to do this?

       

      Thanks and kind regards

       

      Mel

        • Re: Set analysis conflict
          Ruben Marin

          Hi Mel, your expression can work like this?:

          =Sum({<DemandDriver={'Headcount'}>}[HeadcountSum])*
          pow(((($(vNoHRGrowthRate)-1)*([cHeadcount])), YearIndex)


          I separated the first Sum, so each operation has his own set analysis.

            • Re: Set analysis conflict

              Hi Ruben,

               

              This is Tom, I'm working with Mel.

               

              I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

               

              In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

               

              Thanks,

               

              Tom

              • Re: Set analysis conflict

                Hi Ruben,

                 

                I'm afraid that doesn't work as ([cHeadcount]) is a field value that changes depending on which [HeadcountSum] is being used.

                 

                In other words, if I close off the first part of the formula as you show, then it doesn't know which [cHeadcount] value to use and returns a null.

                 

                Thanks,

                 

                Mel