2 Replies Latest reply: Sep 15, 2011 2:13 PM by Marc Livingston RSS

    Set analysis/Running total from the top dimension to lower dimensions pivot table

      I need to Pass a total from a pivot table dimension to the lower dimensions to use in a formula.

       

      Current Dimensions:

       

      Sales

      Cost of Goods Sold

      Selling

      General and Admin

       

       

      I want the Sales minus the totals from each column.

       

      Sales                                   155

      Cost of Goods Sold             200         155-200

      Selling                                  168         155-168   

      General and Admin              654         155-654

       

      I tried this, but it is not working, just shows 0 for the Sales so I get - values of the first column

      =sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt)) - sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3,creditamt+debitamt))
      

       

       

      I was able to do this is Crystal Reports using a running total that is summarizing only the sales, and then passing it down. How could I do this here?

       

      Image for visualization:

      untitled.JPG

        • Set analysis/Running total from the top dimension to lower dimensions pivot table

          Ok I somewhat got this to work using a 'Ready to Calculate' button that sets variables when all the selections are made. Would prefer if this was automated, but it is working for now.

           

          Anyone have any other ideas?

          • Re: Set analysis/Running total from the top dimension to lower dimensions pivot table

            So my supervisor does not like the solution of having to click a button to get this to work.

             

            Is there any way to set a variable based on if the value changes?

            Example:

            Dimension  balance

            Sales          52,000

            COGS        25,000

            Selling        10,000

            Gen Admin 5,000

             

            I need to calculate Margin, Only for COGS. For this formula, I need the sum(balance) for only Sales.

            This Formula is not working:

             

            =if(Topincome='Cost of Goods Sold',
            
            (sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1)
            
            -(Sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))))
            

             

             

            However this formula:

             

            =if(Topincome='Cost of Goods Sold',$(Sales)-(Sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))))
            

             

            with the variable set to:

             

            =sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1
            
            

             

            Works perfectly.

             

            Can anyone help me out?