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.
|Project||Start Date||End Date||Budget Allocated||Month||Projected Budget||Actual Budget|
(at start of month)
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])
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?