11 Replies Latest reply on Aug 10, 2017 10:21 AM by Hari Ankem

# How to Sum two Calculated fields

I am trying to sum two calculated fields and subtract them by 7.5 (hrs in a work day) and I can't seem to get the calculation to work.

Attached is the workbook Worksheet "Productivity". I want to (7.5-sum([Non Productive] + [Productive])) I am attempting to do this with calculated field "Availability".

• ###### 1. Re: How to Sum two Calculated fields

Hi Brian - I would like to help but your workbook opens with a message:

Try EXPORT to a twbx workbook and attaching again

Also I looked at the formulas - there is lot going on but I think your issue is with the way the data is aggregated - because you are on t9 the options are somewhat limited

you have TASK as either Productive or Non-Productive and your formulas are at the dis-aggregate level - then you create a formula that 7.5 - the sum of productive + non productive ----- to yield availability - so in word you have a 7.5 hour shift and the tasks (both productive and non productive) equal some value and what is left is available

OK So What - when you drag that to the Viz it is being aggregated again as a sum - does that make sense?

I think you want to use a fixed LOD expression in the form of

{Fixed  [Task],[ You need some dimension that is day/shift}  :sum(7.5-sum(non-productive) - sum(Productive)) }

you will probably get a can not mix aggregate and disaggregate level values error but you will have to play with the syntax -

what you will end up with is the value that is available for each day/shift task permutation - you can then drag that to the viz and sum over all task (if that is what you want) or shifts or days etc

Let me know if this helps

Jim

• ###### 2. Re: How to Sum two Calculated fields

Thanks, let me attach again.

• ###### 3. Re: How to Sum two Calculated fields

Try changing the availability formula as shown here, and remove and replace the Availability measure again to view the output as shown.

• ###### 4. Re: How to Sum two Calculated fields

Thanks for attaching a new file

I'm a little overwhelmed with the detail and the calculations but the issue is with your red pill and the aggregation of the Productive and non productive values - you are pulling them off the same Total time

in each calculation you need to add an ELSE 0 clause to avoid creating NUlls

non-productive         if contains([Task],"Non Prod") then [Total Time] else 0 end

Productive                if contains([Task],"Non Prod") = false then [Total Time] else 0 end

and the availability                (7.5-sum([Non Productive]+[Productive]))

Then you will end up with this

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.

1 of 1 people found this helpful
• ###### 5. Re: How to Sum two Calculated fields

Hey Hari,

Did n't see u around for a while?

Deepak

• ###### 6. Re: How to Sum two Calculated fields

Was on vacation for 3 weeks.

• ###### 7. Re: How to Sum two Calculated fields

Welcome Back!!

• ###### 8. Re: How to Sum two Calculated fields

Thank you.

• ###### 9. Re: How to Sum two Calculated fields

Jim, thank you very much this worked!

• ###### 10. Re: How to Sum two Calculated fields

Hari, thank you very much this worked!

• ###### 11. Re: How to Sum two Calculated fields

You are welcome.