6 Replies Latest reply: Jun 19, 2012 12:06 PM by Revlin Abbi RSS

    Sum only grouped by one field? (Part 2)

      Hi,

       

      I asked a question yesterday, which has been answered but I have an additional question that takes it that step further. I will repeat the first question then move on to my actual question:

       

      First question:

      Say I have a straight table with 4 fields:

       

      year,

      quarter,

      month,

      quantity,

       

      I would like to add a 5th field which is an aggregate function but only grouped by the year, i.e. I want to add quanity_year which is the sum of quantity for the year. Can I do this using an expression?

       

      example data below (for illustrative purposes):

       

      table

      year     quarter     month     quantity     year_quanity

      2011     1               1              3               14

      2011     2               .               5               14

      2011     3               .               6               14

      2012     1               .               8              

       

      ANSWER: defining year_quantity as an expression defined as =sum(TOTAL <year> quantity). This works fine and achieve what I required as long as the year field is included in the straigh table.

       

      Actual question:

      Say I have the same data (as in table above) but this time I want to have a concatination of year and month as one field, then the sum of quantity. In other words, I want to remove the fields year, quarter, month and only have 3 fields, as below.

       

      year_month     quantity     year_quanity

      2011     1         3               14

      2011     .          5               14

      2011     .          6               14

      2012     .          8              

       

      This however does not work as now the field year is not included in the straight table and the year_quantity expression stops working. The expression ends up giving me a total quanity over the whole dataset and does not factor in year at all.

       

      Any ideas on how I can get the sum of quantity grouped by year but without actually including the year in the straight table? I know I could do this as a preporcessing step (say via sql) or via the load script but I was hoping to do this via an expression.

       

      Regards

       

      Revlin