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

    Need help with calculated field for totaling specific values

    Mark Goulbourne

      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
          Ashish Chaudhari

          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
            Mark Goulbourne

            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!