Thanks for your response. You workbook looks interesting but I have a few questions:
1. Do I need to manually set the filter for Sales Date to filter the Sales records?
2. If I have another Opportunity record for another customer ID or product ID with different Close Date (let's say Sep 1st), will this work? What if there is no sales for the new Opportunity?
1. Yes you've to use your Sales Date to filter the sales data which will be static filter. If you wants to make it dynamic then you should go with parameters.
2. As per your requirement
So, you're going to miss Sep month from the view. If any new opportunity comes in between Oct-Dec months with no sales then you can see that record in the view.
FYI, established the relationships on Month and Year like this
Let me know If this help.
Thanks for the explanation, Mahfooj.
If I want to filter on the Close Date (for example, opportunities closed in current year) and track the following 12 months sales of each opportunity product, how could I make it happen? I think I can use parameter to filter the Sales Date but in the above case, the Close Date is dynamic and the following 12 months period for each opportunity is also dynamic.
Also, if I use Sales table for the main data source and there will be customers and products not in the Opportunity records, how can I filter or hide these records?
In that case you use this option. Here filter will apply on both the sources.
To hide the null drag field from secondary sources to filters shelf and exclude null.
After analysing your requirement I guess you should use Custom SQL join to combine both the sources. Don't use blending here. Which data source are you using? if you're using any db like oracle or MySQL then use database join and create a view then connect to tableau.
If you're using then put your data in a single workbook separated in sheets. Then connect to tableau with legacy connection and use Custom SQL join to combine both sources. In that way you can think of.
The Opportunity Data is from Salesforce.com and the Sales data is in SQL server. That's why I can't Join both sources upfront. I'm trying to figure out if there is any workaround by using blending.
You can use cross database join. I guess you're on v10.1
Sent from my iPhone