4 Replies Latest reply: Aug 24, 2012 11:41 AM by Bruno Santos RSS

    Conditional Sum

    Bruno Santos

      Dear all,

       

      I'm facing the follwing problem.

       

      I've a pivot table with two dimensions:

      WorkType, [Division-> Depart-Employee]

       

      Worktype as column

      Group [Division-> Depart-Employee] as lines

       

      The metric is hours of work.

       

      I need to create an expression that sums some types of worktype and that its results appear in all columns.

       

      I built the following expression, but the value (although well calculated) only appears for the first condition worktype instead of repeating in all columns.

       

      sum
      ( aggr( sum(if(WorkType='B' or 
      WorkType='C' or 
      WorkType='D', Hours)),[Division->Depart->Employee])) 
      
      

       

       

       

      How can I solve this requirement ?

      Help please

      Thanks in advance

      Bruno

        • Re: Conditional Sum
          Jonathan Dienst

          Hi

           

          If you use Aggr() inside a chart or table, then you must include all the table dimensions in the Aggr(). In your case, Add WorkType...

           

          sum ( aggr( sum(if(WorkType='B' or WorkType='C'

          or WorkType='D', Hours)),[Division->Depart->Employee], Worktype))


           

          Hope that helps

          Jonathan

            • Re: Conditional Sum
              Bruno Santos

              Hi Jonathan,

               

              Thanks your help, but does not have the expected behavior...

              The need is that the sum of the columns (B, C and D) are visible in all columns.

               

              Is just adding the corresponding value from column.

               

              Thanks

              Bruno

               

              image.jpg

                • Re: Conditional Sum
                  Jonathan Dienst

                  Bruno

                   

                  I assume that Cond Sum is the expression with the problem?

                   

                  If you need data from B, C and D to appear in each case, then try this:

                   

                  sum ( aggr( sum(Total <WorkType> if(WorkType='B' or WorkType='C'

                  or WorkType='D', Hours)),[Division->Depart->Employee], Worktype))


                   

                  Regards

                  Jonathan

                    • Re: Conditional Sum
                      Bruno Santos

                      Hi Jonathan,

                       

                      I had to change a little because I wanted to sum lines and not columns, but it worked perfectly.

                      Thanks a lot

                       

                      Bruno

                      sum(Aggr( sum(TOTAL <[Division->Depart->Employee]> if(WorkType='B' or WorkType='C' or WorkType='D'
                      , Hours,0)),[Division->Depart->Employee],WorkType ))