2 Replies Latest reply: Jul 16, 2013 2:38 AM by Kaushik Solanki RSS

    How to sum distinct dimensions

      Hi there,

       

      I have a table like this:

       

       

      Year
      Month
      Field 1
      Field 2
      Field 3
      Vol
      201211SGL100
      201211SOL150
      201211SGC100
      201211SOC150
      201211SPC200
      20131SGL100
      20131SOL75
      20131SGC100
      20131SOC75
      20131SPC200
      20131SSC75

       

       

      And I want to get the sum of all the distincts Field 2, like this

       

      Year

      MonthField 2Vol
      201211G100
      201211O150
      201211P200
      Total

      450
      20131G100
      20131O75
      20131P200
      20131S75
      Total

      450

       

      to create a table which displays the sum (vol) by Field 3 and the Total Vol (sum all distincts Field 2) by year, exactly like this:

       

      Field 1Field 3Year - MonthVolTotal Vol
      SL2012 - 11250450
      SC2012 - 11450450
      SL2013 - 01175450
      SC2013 - 01450450

       

      I've tried

      Total vol = sum(TOTAL aggr(sum(distinct Vol),Field 1,Field 2) )

       

      but only works when I select a year and a month

       

      Thanks,

      Bere