6 Replies Latest reply: Mar 23, 2011 5:59 AM by Anders Schaeder RSS

    Expression: Sum column if row is unique

      I have three columns for arguments sake I will call them A, B, and C these fields are pulled in from a CSV file. The columns B and C need to be shown in a chart showing the following expressions

      1) Sum(C)

      2) The second Expression is what I am stuck on.

      I need to show a sum of B but only if the row has a distinct combination of A, B and C.

      Any help on where to start would be appreciated.

      A: Is a workID

      B: Is the total hours allocated to that workID

      C: contains how many hours an employee worked on that workID

        • Expression: Sum column if row is unique
          John Duffy

          Hello.

          Could you attach a sample application using inline load or give an example of the input data (actual values) and required chart.

          Thanks.

            • Expression: Sum column if row is unique

              Hi,

              Thanks for the reply, here is some sample data

               

               

              WOL.OpNoWOL.StdHoursWOL.HoursWOL.W/Order
              101001.25WS61064
              101003.75WS61064
              101008.75WS61064
              152000.25WS61064
              152007WS61064
              152007.25WS61064
              152007.5WS61064
              152008WS61064
              152008.5WS61064
              152008.75WS61064
              152009.25WS61064
              152009.5WS61064
              152009.75WS61064
              201000.25WS61064
              201000.5WS61064
              201001WS61064
              201001.25WS61064
              201001.5WS61064


               

              WOL.W/Order Is the unique Job

              WOL.OpNo Is an activity that makes up that job

               

               





               

              WOL.StdHours Is the hours assigned to the OpNo



              WOL.Hours Is a running total of hours worked on a OpNo

              The chart needs to show a sum of WOL.Hours which is the easy part the next is what I am stuck on.

              The WOL.StdHours for OpNo 10 is 100 if I simply sum it I get 300 when 100 is the correct figure in this example data I would need the graph to show 400 Hours alloted and 94 Hours taken.

              The OpNo is in no way unique a differnt WOL.W/Order may contain the same OpNo.

              Hope this clears things up and help you help me.

              Regards

              Steven

               

               

            • Expression: Sum column if row is unique
              Michael Solomovich

              The first that comes to mind:
              sum(if(count({1} A&B&C)=1, B, 0))
              I guess it's better to define "uniqueness" in the script rather than in the expression, by counting the number of ABC combinations, and marking as unique if count=1