Can u please attach the workbook..?Also wondering why do we need to aggregate or check for null in order to get valid value..
Of course, workbook attached.
The primary reason why your [difference] calculation didn't work as expected was actually due to how you wrote your [Date's Sales] and [Date-1 Sales] calculations.
Take [Date's Sales] for example:
if [Order Date]=[Order Date Parameter] then [Sales] END
This logic will catch any rows where the order date equals order date parameter, no problem there. But what about the rows that don't equal order date parameter? Since your IF THEN statement does not include an ELSE clause, all of the rows that do not equal order date parameter end up as NULL. You can see this when taking a look at the underlying data:
Your original [difference] calculation had no aggregation: [Date's Sales] - [Date-1's Sales] This means that the calculation occurs at every single row, and since you never have a single row where both [Date's Sales] and [Date-1 Sales] have a value, Tableau is trying to subtract a value minus a null, or a null minus a value. Anytime a null is present the result of the calculation is null, and therefore you see nothing in your Viz.
My two solution options get around this problem differently. The easy one is just using Tableau's ZN() function, which returns a 0 anytime it sees a null. This allows your calculation to work as you originally expected.
The other option was aggregating SUM([Date's Sales]) and SUM([Date-1 Sales]) before carrying out the subtraction. This causes tableau to sum up all of the values within each column, and then carry out the subtraction. (summed up column vs row level calculation)
In summary, there are many ways to get to your desired end result:
1. Add an ELSE clause to [Date's Sales] and [Date-1 Sales]
2. Provide Null handling in the form of ZN() wrappers in your [difference] calculation
3. Aggregate your measures before carrying out your subtraction
For more information around row level vs aggregated calculations I highly recommend taking a look at this free Tableau training video: Aggregation, Granularity, and Ratio Calculations | Tableau Software
DateIssue_edit_2.twbx 1.4 MB