Continuing the last post on Tableau Relationship, I would like to discuss about Join here. We can achieve the same result as previously discussed in the last post using Join feature. However, we have to choose by ourselves the type of join. We used left join to match the daily granularity of the sales data.
If we take a look at the daily sales and monthly target after join, it shows us different value. For example, the target sales for June 2013 is 227,357, while it should be 11,368. Why is it?
It is because for each day, Tableau assigns the same target value of 11,368 and added those value for a month resulting in the monthly target value of 227,357.
Even if we change the target to average (as we might think, it should be average target and not sum of duplicated target value), we still don’t get the right value. It even gives us another value of 1,895. Where does this value comes from?
In order to investigate further, pull company column into the table. It turns out that 1,895 comes from total target for June 2013 for all 6 companies, so 11,368 / 6 = 1,895. We have to remember that each data point of target represents each company target for each month. Thus, looking at temporal level (yearly, monthly or daily) without taking into account company level is misleading. We could see Google has the highest monthly target compared to AAPL, for example.
Thus, we have to be careful not to take the wrong value for visualization. Inspect AAPL for June 2013, the target is 18,856 which is a wrong value.
However, we change to average (AVG), it gives us the right value at the company and month level, even if it still gives wrong value when we take company out of the investigation.
Woow, what an investigation! Using Join with different granularities, we have full control of the connection between datasets, however, we have to be really careful and always check if aggregated (or calculated) value is the one we expected.
Comparing to relationship on the previous post, relationship is less complicated than join here in order to achieve the same goal.
Cheers,
Nuki