8 Replies Latest reply: May 16, 2013 8:07 AM by Adam Haithcox RSS

    Sum doesn't "roll up" when collapsed

    Adam Haithcox

      I have a table with 3 dimensions and two expressions. With the dimensions completely expanded, I get the values I'm looking for in "New business" and "Core business", but when they are collapsed, the sum of the Core and New business values don't roll up correctly. New Business gets rolled into Core:

       

      Capture.PNG

      Capture1.PNG

      Capture2.PNG

       

      Here are my two expressions:

       

      Core Business:

           Sum({$*BM08 <[Fiscal Year]={'$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]) - Column(2)

       

      New Business:

           if (Sum({$*BM08 <[Fiscal Year]={'>=$(=FiscalCurrentYear-2)<$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]) <= 0,

                     Sum({$*BM08 <[Fiscal Year]={'$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]),0)

       

      Can someone explain why this is happening and suggest how to fix it? Thanks in advance.

        • Re: Sum doesn't "roll up" when collapsed
          Michael Solomovich

          Why this is happening: I think that the "New Business" expression returns 0 in the collapsed mode because the condition returns false for the overall territory.
          How to fix: If my assumption above is correct, you need not just a sum, but sum of sums grouped by dimensions, e.g.:
          sum(aggr(expression, "Sub Section", "Ship To"))

           

          The "Core Business" expressions probably will be correct after fixing the "New Business".

           

          Regards,
          Michael

            • Re: Sum doesn't "roll up" when collapsed
              Adam Haithcox

              Thank you so much for your reply Michael and PLEASE stick with me to get this figured out; I will be forever in your debt

              Is this what you propose:

               

              if (sum(aggr(Sum({$*BM08 <[Fiscal Year]={'>=$(=FiscalCurrentYear-2)<$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]) <= 0,[Sub Section],[Ship To])),

                        sum(aggr(Sum({$*BM08 <[Fiscal Year]={'$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]),[Sub Section],[Ship To])),0)

               

              I have tried it, but the results weren't correct and there was an immense amount of processing.

              • Re: Sum doesn't "roll up" when collapsed
                Adam Haithcox

                I was able to get my expression working correctly. Unfortunately, when I collapse the dimensions, it still does not roll up. I've determined that this is caused by the IF statement. If I remove the IF and use only the expression, the values roll up (although incorrect). Is there a way to write the same expression without using the IF statement? Like with an e() in set analysis? Here is the expression:

                 

                If(Sum(aggr(Sum({$*BM08 <[Fiscal Year]={'>=$(=FiscalCurrentYear-2)<$(FiscalCurrentYear)'},Month={'*'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]),[Sales Catalog Sub Section],[Ship To]))<=0,

                Sum(aggr(Sum({$*BM08 <[Fiscal Year]={'$(FiscalCurrentYear)'},[Customer Sub Type]-={'ENT-Human'}>}[Sales Amount]),[Sales Catalog Sub Section],[Ship To])),0)