6 Replies Latest reply on Sep 27, 2018 6:15 AM by Jim Dehner

    Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

    Mohammad Jahanzeb

      Hello,

       

      I have an issue where I am trying to aggregate the sum of two values which have the same JOB, SUFFIX, ITEM, and OPERATION but has both completion as a "0" or "1"

       

      See an example below:

       

             

      JOBSUFFIXITEMOPERATIONCOMPLETE?HOURS
      10Table1403.5

       

      *Need to aggregate these two values to display 1 value of the sum of both

      14.5
      20Chair517.6
      30Lamp705.2

       

      So here for Job 1, it has the same suffix, item, and operation, I'm trying to aggregate those two hours (Which are already a Sum) to display 1 value as a sum of both.

       

      Goal is to keep a job which has been Completed and aggregate their hours.
      If there is a Job, like Job 3, which has a complete 0 and not a 1 yet, indicating that the job is not done yet, we want to take those out of consideration.

      On the other hand, If a Job is complete, like Job 2, we want to keep that in.

       

      I've tried multiple calculations like LOOKUPs, FIXED, IFs and more but I can't really decipher how to approach this calculation.
      Does anyone have an idea?
      Unfortunately, I won't be able to share a workbook for this.

       

      Please let me know.
      Thank You!