Hi
In tableau, how could I join two fact table with a common dimensional table at different granularity to create a view.
I have two fact tables:
1) daily sales: sale # at daily level
2) Monthly target: target # for each month.
I have one dimensional table, dDate, which is linked to the two fact tables at different granularity.
dDate has one date per row in the table, the fields in dDate:
DayDate, WeekDate, Month, Year ....
The daily sales is mapped to DayDate field, and monthly target is mapped to Month field.
When I join the 3 tables together in Tableau to create a view, comparing monthly sale and monthly target, The aggregation of target # is counted at daily level instead of monthly level. That completely screwed up the number.
What do I miss here?
How to create a view to compare the aggregated daily sales to month level with monthly target?
Thank you
Hey Ken -
Another approach you could take is to simply NOT join all three tables together. Instead:
- Join your date dimension and daily sales fact table together in one data source
- Bring in your monthly goal fact table in as a second, distinct data source
- Use Data Blending to marry the two together.
I've attached a sample, including the 3 tables (in an Excel worksheet) that I created to drive the sample....
