8 Replies Latest reply: Jul 28, 2015 7:21 AM by Rajesh V RSS

    Pivot Table

      Hi All,

       

      I am having a Pivot Table with Location,Name & Date as Dimension and Sum of a column Handled as expression.

       

      Output is as given below.

       

       

      LocationNameDate2015-07-012015-07-022015-07-032015-07-042015-07-05
      BangaloreAgent 1 -21---
      BangaloreAgent 2 49114731104--
      BangaloreAgent 3 -----
      BangaloreAgent 4 500653488--
      BangaloreAgent 5 745758765--

       

       

      Need to add following columns at teh end.

       

      Avg Per DayMaxMin# of days with > 500

       

       

      How do i add this to the Pivot table as last columns post teh dates.

        • Re: Pivot Table
          Manish Kachhia

          Please provide sample data or sample application. You need to use TOTAL Qualifier to achieve this result.

            • Re: Pivot Table
              DateHandledName
              2015-07-01267Agent 1
              2015-07-02254Agent 2
              2015-07-02321Agent 3
              2015-07-02330Agent 4
              2015-07-03396Agent 5
              2015-07-04258Agent 6
              2015-07-06253Agent 7
              2015-07-06315Agent 8
              2015-07-06319Agent 9
              2015-07-07254Agent 10
              2015-07-07294Agent 11
              2015-07-08252Agent 1
              2015-07-08253Agent 2
              2015-07-08271Agent 3
              2015-07-08361Agent 4
              2015-07-09286Agent 5
              2015-07-10385Agent 6
              2015-07-13260Agent 7
              2015-07-16260Agent 8
              2015-07-17306Agent 9
              2015-07-17315Agent 10
              2015-07-17366Agent 11
              2015-07-20253Agent 1
              2015-07-20272Agent 2
              2015-07-20311Agent 3
              2015-07-20347Agent 4
              2015-07-22297Agent 5
              2015-07-23253Agent 6
              2015-07-23309Agent 7
              2015-07-23463Agent 8
              2015-07-24266Agent 9
              2015-07-25272Agent 10
              2015-07-26253Agent 11
              2015-07-26291Agent 1
              2015-07-26335Agent 2
              2015-07-26418Agent 3

               

              Please use the above sample data, Ignore location which i will add it later.

                • Re: Pivot Table
                  Manish Kachhia

                  Use Straight Table

                   

                  For Average

                  =SUM(TOTAL <Name> Handled)/COUNT(TOTAL <Name> Date)

                   

                  For Min

                  Min(TOTAL <Name> Aggr(SUM(Handled),Name,Date))

                   

                  For Max

                  Max(TOTAL <Name> Aggr(SUM(Handled),Name,Date))

                   

                  For >= 500

                  SUM(TOTAL <Name> Aggr(IF(SUM(Handled)>=500,1,0),Name,Date))

              • Re: Pivot Table
                Prashant Sangle

                Hi,

                 

                If Date is also your dimension then i dont think so it is possible to do required thing in Pivot table.

                 

                You can try in Straight Table by creating each date value as different Expression.

                 

                Regards