4 Replies Latest reply: Dec 17, 2012 1:46 PM by chand S RSS

    Pivot Table - Total problem

      Hi guys:

      I have  a problem, which i thought will be a small issue but it is driving me crazy.

      Please help.

       

      I am using a pivot table.

       

      I have 3 dimensions say Dim1 , Dim2 and Status .I have an expression Sum(m1)

       

      the totals in this case come correct.

          Dim1   Dim2  Status      Sum(m1)

            A        AA      T1               10

            B        BB       T2               20

            C        CC       T3               30

      Total                                    60

       

      but my requirement is that when status = 'T2' then Sum(m1) should display 0 and should not be added to the total.

       

      but the strange thing is when i write my expression-   

      If(Status = 'T2',0,Sum(m1))

       

      My 2nd row(Dim1=B,Dim2 = BB,Status = T2) is displaying 0 but total is still coming as 60. I would have expected it to be 40.

       

      did you guys face this problem.

       

      Please suggest what i am missing here.

        • Re: Pivot Table - Total problem
          Miguel Angel Baeyens de Arce

          Hi,

           

          Try using instead:

           

          Sum(If(Status = 'T2', 0, m1))
          

           

          Hope that helps.

           

          Miguel

            • Re: Pivot Table - Total problem

              Miquel:

              Thanks for the quick response.

              In my question earlier, i had written simple if for the sake of example.

               

              My actual Expression has set modifiers also, so i have something like

               

              if(Status_SOURCE = 2,0,Sum({<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT))

               

              so now if i apply your suggestion,how the expression should look

               

              Sum(if(Status_SOURCE = 2,0,Sum({<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT)))

               

              or Sum(if(Status_SOURCE = 2,0,{<ADGT_TYPE = {'Budget'},AMT_ID = {'Month'}>}AMOUNT)))

               

              both of these give me error.

                • Re: Pivot Table - Total problem
                  Miguel Angel Baeyens de Arce

                  Yup,

                   

                  Syntax with set modifiers and a conditional is a bit different:

                   

                  Sum({<ADGT_TYPE = {'Budget'}, AMT_ID = {'Month'} >} If(Status_SOURCE = 2, 0, AMOUNT))
                  

                   

                  In the future, think of writing the expression with a field, then replace the field with the If() to make things simpler.

                   

                  Hope that helps.

                   

                  Miguel

                    • Re: Pivot Table - Total problem

                      Miquel:

                      if i follow the above syntax, my Amount is kind of doubling up.

                       

                      I also used your suggestion of using the field i.e.doing my calculation in the script and replacing it in the if()

                      It is still showing huge amount.

                      I calculated a field with all my conditions  as F1_Amount

                      In Pivot expression, if i use

                       

                      If(Status_SOURCE = 2,0,Sum(F1_Amount)) - I dont get the desired result i.e. i see 0 but the original amount gets added up to the total.

                       

                      if i use Sum(if(Status_SOURCE = 2,0,F1_Amount)) -  amounts get doubled up