-
1. Re: How to join two fact table with a common dimension at different granularity
Jonathan DrummeyOct 17, 2012 10:55 AM (in response to Ken Zheng)
1 of 1 people found this helpfulHi,
Depending on how you are setting up your view, you might be able to use an aggregate like MIN() or ATTR() on the monthly target when SUM(Daily Sales) gets you that total.
If that doesn't work, then you may need to use a table calculation to accomplish your aggregation, in that case posting a packaged workbook (.twbx) with some sample data would be good.
Jonathan
-
2. Re: How to join two fact table with a common dimension at different granularity
Ken Zheng Oct 17, 2012 12:58 PM (in response to Jonathan Drummey)Hi Jonathan,
Thank you for your prompt answer. The MIN() and ATTR() are helpful at the first place, however, it is not working once I try to do some aggregate on those monthly target.
For example, the target # is stored at per product per month, and I could use ATTR() or MIN() to get the correct number displayed at a month/product pivot table. However, when I try to get the total target number for each month, across products, the function didn't give me the number I want.
How to do monthly target number in this case ?
Thank you
-
3. Re: How to join two fact table with a common dimension at different granularity
Russell Christopher Oct 17, 2012 1:29 PM (in response to Ken Zheng)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....
-
SampleData.xlsx 12.8 KB
-
SampleSolutiuon.twbx.zip 28.9 KB