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

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:

 JOB SUFFIX ITEM OPERATION COMPLETE? HOURS 1 0 Table 14 0 3.5 *Need to aggregate these two values to display 1 value of the sum of both 1 4.5 2 0 Chair 5 1 7.6 3 0 Lamp 7 0 5.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.

Thank You!

• ###### 1. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

Hi

I would like to see your twbx workbook - what does this mean "I'm trying to aggregate those two hours (Which are already a Sum) "

is the value of Hours a calculated field?

Jim

• ###### 2. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

Like I mentioned I cannot share the workbook due to sensitive data.

Hours is not a calculated field.
The hours you see there are already a SUM of hours.
that means that Job 1 with Completed 0 could have posted multiple hours and that is an aggregated hours of that.
What I want is to aggregate the aggregated hours of the Completed 0 and 1.

• ###### 3. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

then one solution is an lod that looks like this

{fixed [job],[suffix item] , [operation]: sum(hours)}

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

Hello,

You can try below approach:

1. This one is to get the maximum value for [Complete?] for each [JOB],[Suffix],[Item],[Operation] combination.

max complete = {FIXED [JOB],[Suffix],[Item],[Operation]: max([Complete?])}

2.  This one is to remove "not complete" Jobs.

Flag = if [max complete]=1 THEN TRUE ELSE FALSE END

Arrange the values as shown below:

• ###### 5. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

Hi Meenu,

Thank you for your response but the thing is you are summing the hours which gives you this.
I'm saying the 3.5 and 4.5 hours you see are already a Sum aggregate. I want to SUM those aggregated sum again together and then only show the fields with Complete? as 1 (without losing that aggregated sum of sums).

I had tried a similar method on this before as well.

• ###### 6. Re: Aggregating Sum of two values if a Concatenated field has two values present for a specific Dimension

the 'Already summed" summed values are the result of a calculated files?  if so convert you calculation to an LOD - (without seeing your book I can't write it for you) but the LOD will sum individual values to create a new level of data but that level is NOT aggregate

you can then sum the LOD level with a simple sum or you can nest it in an LOD like shown

{fixed [job],[suffix item] , [operation]: sum(LOD hours)}

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.