4 Replies Latest reply: Mar 21, 2012 1:06 PM by marcelo gonzalez RSS

    Help with avg in subtotal pivot table

    marcelo gonzalez

      Hi all!

      I need help with the presentation of a subtotal in a pivot table:

      I calculate the average of a series of percentages (obtained from an expression whith set analysis )

      The pivot table gives me back a total of 40% (maximum value of the column) when I need is an average ( 27%)

      here is the pivot table results:


      dimension 1           20%
      Dimension 2           20%
      dimension 3           2
      0%
      dimension 4           30%
      dimension
      5           30%
      dimension 6           40%
      TOTAL                    40%

      when i need
      Average 27%

       

      In the forum, I've seen expresions with dimensionality () = 1 or = 2. but I could not get the average

       

      I copy the expression I'm working with:


      if (Dimensionality () = 1,

      0,
      (sum ({$ <AñoFactura = {$ (= max (AñoFactura))}>} Executed)
      / sum ({$ <AñoObjetivo = {$ (= max (AñoFactura))}>} Target))

      )

      Any
      suggestions?

      excuse my english!!!!

       

      Thanks!

        • Help with avg in subtotal pivot table

          What is the dimensionality of your sub total?

           

          It is returning 40 because it is doing the total executed/ total target.

           

           

          You my be able to use aggr.

          if (Dimensionality () = 1,

          0,

          Avg(aggr(

          (sum ({$ <AñoFactura = {$ (= max (AñoFactura))}>} Executed)
          / sum ({$ <AñoObjetivo = {$ (= max (AñoFactura))}>} Target))

          , Dimension))

          )

           

          This (I believe, not sure without testing) should return the correct results for each dimension and then do the average for the sub total

            • Help with avg in subtotal pivot table
              marcelo gonzalez

              Thanksfor answering. I send you the real expression to be more clear:

              Theexpression I’m using is the following (whit out dimensionality()) :

               

               

              if((VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo))>1,1,

              (VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo)))

               

              ItWorks just fine but returns the following :

               

                                                             

               

              Vendedor

               
               

              Cumplimiento T1

               
               

              Vendedor1

               
               

              100%

               
               

              Vendedor2

               
               

              0

               
               

              Vendedor3

               
               

              54%

               
               

              Vendedor4

               
               

              0

               
               

              Vendedor5

               
               

              23%

               
               

              Vendedor6

               
               

              64%

               
               

              Vendedor7

               
               

              51%

               
               

              Vendedor8

               
               

              24%

               
               

              Vendedor9

               
               

              42%

               
               

              Vendedor10

               
               

              2%

               
               

              Total

               
               

              60%

               
              • When I need 45% as Total AVG

               

               

              I’vetried your recommendation as follow:

              Avg(aggr(

              if((VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo))>1,1,

              (VendidoT1/sum({$ < TrimestreObjetivo={'T1'}, AñoObjetivo={$(=max(AñoFactura))}>}Objetivo)))

              ,Vendedor))

               

              Butthis is the result:

                                                             

               

              Vendedor

               
               

              Cumplimiento T1

               
               

              Vendedor 1

               
               

              0

               
               

              Vendedor 2

               
               

              0

               
               

              Vendedor 3

               
               

              0

               
               

              Vendedor 4

               
               

              0

               
               

              Vendedor 5

               
               

              0

               
               

              Vendedor 6

               
               

              0

               
               

              Vendedor 7

               
               

              0

               
               

              Vendedor 8

               
               

              0

               
               

              Vendedor 9

               
               

              0

               
               

              Vendedor 10

               
               

              0

               
               

              Total

               
               

              0

               

               

              Anyidea what can be happening ?

              Idon’t know if using aggr() function with set analysis in it is what generatethe error. It seems that the expression does not resolve the if() part first

               

              Thankyou very much for your interest.