I actually wanted to continue the post with “Join”, however, I think there is still one aspect that needs to be mentioned about “Relationship”. Relationship is able to handle different granularity data with little bit adjustment.
Sales Data – Daily basis
Sales Target - Monthly Basis
We try to connect the daily sales data with monthly sales target in order to figure out whether sales has reached target on the monthly level.
1. If we build relationship between both of the datasets only using company columns, it will give us all the sum of all of the target, which is false.
2. If we build relationship both datasets using company and date (on different granularity level) columns, the target only appear o the first day of the month, which is also false. As we can see, there is null values shown with sum of target of 1,399,902. These are the sum of target where there is no suitable partner of the sales on the first day of the month. For example, we don’t have target for June, September and December 2013 because we didn’t sell anything in the 1st of June, September and December 2013.
3. Lastly, we build the relationship with adjusted the sales date granularity to monthly level.
The reason the calculation is wrapped in the DATE function is that Tableau will always make a DATETRUNC calculation of the Date/Time type, which would be a mismatch to monthly date in the sales targets.
This relationship gives us the same monthly target for each sales day.
There is, however, still null values exist which occurred because sales data ends in July 2020, while target data ends until December 2020. If we sum up those targets, it will result in 310,885.
Cheers,
Nuki