2 Replies Latest reply on Jul 27, 2016 11:28 PM by Mark Goulbourne

# Need help with calculated field for totaling specific values

This is my first question to the community, any help to resolve this issue would be greatly appreciated.

First, here's how's my data is set up:

Expense Type          Actual/Forecast          Month          Value

Expense A                        Actual                     1/1/2016       \$1,000

Expense A                       Forecast                  1/1/2016       \$3,000

Expense B                        Actual                      2/1/2016

Expense B                      Forecast                   2/1/2016       \$3,000

I have been trying to create a calculated field that only uses my "Actual" value only for Expense A  when a value is in the cell and not the "Forecast" value . If there is no value in the cell for the "Actual", the calculated field should use the "Forecast" value until there is a value for the "Actual" for Expense B.

The formula I used is down below, but this did not work. The calculated field still counted the forecast value in my total number even though there is a number for the actual for Expense A (total number should be \$4,000 instead of \$7,000). I would like to do this without changing the structure of the data.

IF STR([Month]) = STR([Actual]) THEN [Value]

ELSE [Forecast]

END

Hopefully this makes sense, let me know if I could clarify.

Thanks!

• ###### 1. Re: Need help with calculated field for totaling specific values

Hi Mark,

Here is the calculation as below. Please find the attached tableau workbook (9.3) for your reference. I have also attached the sample data which you have shared in this post.

IF ({ FIXED [Expense],[Actual/Forecast] : SUM([Value]) })<> 0 then { FIXED [Expense],[Actual/Forecast] : SUM([Value])  }

ELSE {FIXED [Expense] : SUM([Value])}

END

Please refer to the screenshot below. This would take actual values where they are present else the will take the foretasted values when actual is null or 0. You can change the condition to null as well. Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Need help with calculated field for totaling specific values

Hi Ashish,

Thanks for answering my question, but I should add more I should add more clarity to my question, apologies. Please see the data again with the added "Baseline"  values.

Expense Type       Baseline             Actual/Forecast          Month          Value

Expense A              \$6,000                    Actual                     1/1/2016       \$1,000

Expense A              \$6,000                   Forecast                   1/1/2016       \$3,000

Expense B              \$8,000                    Actual                     2/1/2016

Expense B              \$8,000                   Forecast                   2/1/2016       \$3,000

How would you rework the formula with the added data?

Thanks!