
1. Re: Calculations between 2 worksheets
Wilson Po Jun 12, 2017 5:32 PM (in response to Saket Metkari)1 of 1 people found this helpfulHi Saket,
No need for the LOD fixed calculations, from what it looks like you simply need to use boolean statement to replicate the [Work Type] filter you have on each sheet:
SUM(IF( [Work Type] = "BILLABLE" THEN [Hours] END)  SUM(IF( [Work Type] = "PRODUCT WARRANTY" THEN [Hours] END)  SUM(IF( [Work Type] = "SERVICE WARRANTY" THEN [Hours] END)
The results should be the total number of hour remaining. If we call this field [Hours Remaining], you can compute the percentage by comparing to the overall "BILLABLE" again:
[Hours Remaining] / SUM(IF( [Work Type] = "BILLABLE" THEN [Hours] END)
This reading might help with any future logical calculation work you may need to do: Logical Functions

2. Re: Calculations between 2 worksheets
arvindgarg Jun 12, 2017 7:15 PM (in response to Wilson Po)I will also add else 0 in all the if else statements . Because , if it evaluates to NULL . Final calculation will return NULL.
Something Like below
IF( [Work Type] = "BILLABLE" THEN [Hours]
ELSE 0
END

3. Re: Calculations between 2 worksheets
Wilson Po Jun 12, 2017 7:25 PM (in response to arvindgarg)1 of 1 people found this helpfulHi Ari,
That's not necessarily true. Most DBs understand how to aggregate across NULL values in a field without issue; this includes Tableau's data engine when working with flat files or extracts. The only situation where NULLs will result in NULL returns is if it is part of a mathematical operation, such as if one of the above sums are actually NULL: i.e. Value NULLValue= NULL. This might be a valid data problem to call out, so I would not always suggest assuming zeros to complete a calculation is the appropriate approach.
I will also point out that Adding "ELSE 0" will also tend to affect nonadditive aggregation results; if we did AVG() on the fields, it may assuming that non "BILLABLE" rows are valid rows to consider in the Average calculation down and drag our mean results down. Something to consider.

4. Re: Calculations between 2 worksheets
arvindgarg Jun 12, 2017 7:39 PM (in response to Wilson Po)Hi Wilson
I do not disagree with you . But i always use else 0 in these kind of situations . So i replied here.

5. Re: Calculations between 2 worksheets
Shawn Wallwork Jun 13, 2017 7:05 AM (in response to arvindgarg)Huh? Ari why would you 'always use' something that could/will skew your results? If you agree with Wilson, then why continue/promote a 'bad' habit?
Shawn

6. Re: Calculations between 2 worksheets
arvindgarg Jun 13, 2017 6:45 AM (in response to Shawn Wallwork)Sir
I am not promoting anything here. Whenever i used that it may not have skewed the result.
I believe people reply in this forum based on their experience level. Some may have more experience and some may have less.
I agree with Wilson's explanation, which he did after my reply to this thread.
I hope , it clarify the things.
Ari