3 Replies Latest reply on Jun 7, 2018 4:08 AM by Mavis Liu

    Unexpected Behaviour with Fixed LoD

    maksims.fedotovs

      Hello,

       

      I would like to create a calculation for buckets of # of clients. To do that, I use the FIXED LoD calculation (Revenue LOD) to aggregate Revenue. The scope level is either Client or Client and Region level, depending on the Global/Region parameter selection. What I don't understand is why I get 190,626,172 when you select Global. When Region is selected, I get 30,823,502, which is expected. I would expect to get the same 30,823,502 when Global is selected. Also, if I remove 'Region' from the ELSE clause when Global is selected, I get the expected 30,823,502. Could anyone explain how the Region field comes into picture when the parameter is set to 1? I have a workaround but I just wanted to understand this behavior.

       

      Please refer to the screenshot below.

       

      Thank you in advance!

       

        • 1. Re: Unexpected Behaviour with Fixed LoD
          Mavis Liu

          Hi Maksims,

          Please change your logic to:

           

          if [Global/Region] = 1 then SUM({ FIXED [Client] : SUM([Revenue])})

          else

           

           

          SUM({FIXED [Client], [Region] : SUM([Revenue])})

           

          END

           

          So have it aggregate it within the calculation itself. Thanks,

           

          Mavis

          • 2. Re: Unexpected Behaviour with Fixed LoD
            maksims.fedotovs

            Thank you, Mavis! Would you be able to explain why aggregating in the calculation is different from aggregating in the worksheet? Also, I wonder why removing Region from the ELSE clause fixes the problem? Why Region has an impact on the IF clause?

             

            Thanks,

            Max

            • 3. Re: Unexpected Behaviour with Fixed LoD
              Mavis Liu

              Hi Maksims,

               

              Usually it's the same, but in this case it's being very strange when you're doing a field switch using LODs.

               

              So what I gave you in my last post works:

              (1)

              if [Global/Region] = 1 then SUM({ FIXED [Client] : SUM([Revenue])})

              else SUM({FIXED [Client], [Region] : SUM([Revenue])})

              END

               

              Using this next formula, it *should* give the same answers:

              (2)

              SUM(

              if [Global/Region] = 1 then { FIXED [Client] : SUM([Revenue])}

              else {FIXED [Client], [Region] : SUM([Revenue])}

              END)

               

              But it doesn't.

               

              Your original way of doing things is the same as (2), where you had the calculation and then everything was summed. In (1) I'm using the condition and summing just that bit of LOD when it's relevant.

               

              This is something I hadn't seen before and I spoke to a few people and we don't QUITE understand why it would do this! In (2), it seems to take Region into account even if you choose 'Global' in your parameter. Number (2) seems to double count whenever there's more than one region in the row. Unfortunately I'm not entirely sure why it does this...

               

              Thanks,

               

              Mavis