1 Reply Latest reply on May 24, 2016 2:08 PM by Andrew Watson

    IF Statement Filtering Data in Calculated Field

    Josh Leviton

      Hi Everyone,

      First of all, this community and forum are immensely helpful, so thanks to everyone for being so active.

       

      My question has to do with using an IF statement for a calculated field, because it seems like the IF statement is filtering our data, which we don't want. I have a bunch of data that compares a projection/forecast and an actual value for each item across the calendar. These two values are manually entered into a table. A table of sample data is below, pretending that we don't yet have the actual budget for March yet.

      ProjectStart DateEnd DateBudget AllocatedMonthProjected BudgetActual Budget

      Remaining Budget

      (at start of month)

      Project 11/1/163/31/16$40,000January$9,000$40,000           $40,000
      Project 11/1/163/31/16$40,000February$10,000$12,000$31,000
      Project 11/1/163/31/16$40,000March$20,000$19,000
      TOTAL$40,000$40,000$21,000

        

      Currently, we have a chart that shows a "Projection" line and an "Actual Budget" line to compare how the Projection matches the Actual Budget. However, we're also looking to add a dynamic forecast line, as detailed below. The point of the line is to "re-distribute" the remaining money evenly across the future to show how much money we have to work with. So for the Project 1 data, all points in the past will be equal to the Actual value from that month. The line in the future will divide up the "Remaining Budget", which can be calculated by doing [Budget Allocated] - SUM([Actual Budget]). The calculated field looks like this:

       

      Dynamic Forecast Line (Calculated Field)                                        

      IF [Date] < TODAY() THEN [Actual Budget]

      ELSE [Remaining Budget]/DATEDIFF('month',TODAY(),[End Date])

      END                                                                                                      

       

      Using the sample data, the data is getting filtered. For instance, if we say TODAY() is in late February, we'd want to assign all remaining spend to the month of March. However, because the data is in the IF statement, the remaining spend assigned to March is only $6,333 (which is 1/3 of $19,000 because there are 3 months), when we want it to be the full $19,000.

       

      In reality, Tableau is multiplying the Remaining Budget by [Number of Months Left]/[Total Number of Months], in Tableau code: DATEDIFF('month',TODAY(),[End Date])/DATEDIFF('month',[Start Date], [End Date]). I tried multiplying our calculated field by the reciprocal but the numbers still don't work...

       

      So.....after that lengthy explanation, can anyone provide insight into why Tableau's IF statements also filter your data?

       

      Thanks!