1 Reply Latest reply: Mar 1, 2017 4:33 AM by Sunny Talwar RSS

    Total and group by function

    Rakesh Pareek

      Hi,

       

      I want to total of some values with respect to dimension. So in front end I am using TOTAL function, but I want to total the values with respect to dimension at script level. As we cannot use TOTAL function in Script. So I am using group by function.

      The issue with group by function is that: I have about to 70 fields and when I am using group by function with all of the fields then server going in hang situation.

      When I am using group by function wrt 3-4 dimentions/fields then I cannot explore my data wrt other dimensions.

      So kindly suggest any other method.

       

      Below is my output from front end:

       

      COEP_Cost_Center Allocation Sum(Cost_INR) sum(distinct ProdQty) Sum(Cost_INR)*sum(distinct ProdQty)/sum(total<COEP_Cost_Center>ProdQty)
      40026197.43 34029.474
      1001100109100110010113342065.812725.3061068521.1297826
      1001100109100110010213342065.8130279.69711871876.423387
      1001100109100110010313342065.811024.471401668.25683043

       

      Below is the script and output:

       

      COEP3:

      LOAD

      COEP_Cost_Center,

      Allocation,

      Sum(Cost_INR)*sum(distinct ProdQty) as Qty1,

      sum(ProdQty) as Qty2

      Resident COEP2

      Group by COEP_Cost_Center,

      Allocation;

      DROP Table COEP2;

       

      COEP_Cost_Center Allocation Sum(Qty1)/Sum(Qty2)
      Sum(Qty1) Sum(Qty2)
      16189.30 454023481587.68 28044665.77
      1001100109100110010115896.4336361212004.392287382.54
      1001100109100110010215727.80403993710080.8625686594.73
      10011001091001100103193363.2713668559502.4470688.50

       

      The Red colored values are by group by function.

        • Re: Total and group by function
          Sunny Talwar

          Seems like this will have to be a two step process

           

          COEP3:

          LOAD

          COEP_Cost_Center,

          Allocation,

          Sum(Cost_INR)*sum(distinct ProdQty) as Qty1

          Resident COEP2

          Group by COEP_Cost_Center, Allocation;

           

          Left Join (COEP3)

          LOAD COEP_Cost_Center,

          sum(ProdQty) as Qty2

          Resident COEP2

          Group By COEP_Cost_Center;

           

          DROP Table COEP2;