3 Replies Latest reply on Sep 11, 2018 12:35 PM by Okechukwu Ossai

    adding curly braces changes result of SUM()

    jon rios

      i have a SUM calculation that works, number is result i am looking for... I am trying not to aggregate it. I will need to use it in another calculation using a parameter whcih gives aggregation errors...  I add curly braces {} around each num and denom.  i also tried with FIXED wording, but i get different results

       

      10000*

      ( SUM(if Value2 = 1 and Value1 = 0

      and [Observation Period] = 'Quarter' [Population Filter] = 'Overall'

      THEN [Reprice Sum]  ELSE 0 END )

      /

      ( SUM(if Value2 = 1 and Value1 = 0

      and [Observation Period] = 'Quarter' [Population Filter] = 'Overall'

      THEN [Balance Sum]  ELSE 0 END )

      )

       

      if i change it to a non-aggregate, by adding curly braces before the SUM in each numerator AND denominator i get a different number... i assumed i would just convert and not change the result.  WHY?  better yet how can i change to non-aggregate while maintaing the SUM.  or can you suggest to re-write more efficiently.  thank you

        • 1. Re: adding curly braces changes result of SUM()
          Deepak Rai

          {} these are for LOD and Once You add, You are looking into Entire dataset and dimension Filters won't work unless you add them to context.

          • 2. Re: adding curly braces changes result of SUM()
            Okechukwu Ossai

            Do you have any filters in the view?

             

            {SUM([Sales])} is the same as {FIXED : SUM([Sales])}.

             

            This calculates the sum of sales for the entire dataset or literally fixed to the entire dataset. It gives a non-aggregate sum of sales. However, FIXED LOD is higher in the order of operations above dimension filters. This means that LOD sum of sales will often not respond to the filters in your view. To get the correct result, you need to add all the filters in your view to context.

             

            Also, LODs makes it possible to display values in a different level of detail than than the one in the view. For example, if you have Region and Category in the view and you want the sum of sales to be broken down into that level of detail, then {SUM([Sales])}, which is fixed to the entire dataset will give you wrong result. You can change the formula by adding the dimension filters and other dimensions in your view to the LOD expression, like below.

             

            {FIXED [Region], [Category]: SUM([Sales])}.

             

            Hope this helps.

            Ossai

            • 3. Re: adding curly braces changes result of SUM()
              Okechukwu Ossai

              I didn't refresh the screen before posting. Glad the question is already answered.