I do not know your actual requirements. Based on my understanding, I loaded data using a left join instead oc concatenate assuming a LeadID must exist before it can be converted into an opportunity. The qvw file is attached.
Test17g.qvw 132.8 K
A problem with your structure is that both tables have two fields in common. Assuming LeadId is the real connection between the two tables, you should give the dates different names (leadDate and oppDate?).
To calculate what you want you make a (pivot or table) chart, select leadDate as dimension and enter two expresions:
think you can ditch the distincts in this case, but I like them as they help readability.
Is that what you were looking for?
Well, connection them to one and the same Date dimension is rather difficult in QV, as typically QV does not allow you to create loops in the model.
you have options:
- create 2 seperate time dimensions
- convert the second date to a meaure, i.e. daysToOpp by calculating the time difference between the dates.
- Leave dateOpp without a date dimension, you can always dynamically derive it if needed.
- creating a concatenated link table.
I kinda like the second option, as it gives you new info, and you can always reconstruct the original oppDate.
The fourth option would give you what you want, your structure would be:
LinkKey, LeadId, Date
1|1/1/2010|, 1, 1/1/2010
2|1/1/2010|, 2, /1/2010
3|1/1/2010|, 3, 1/1/2010
1||1/2/2010, 1, 1/1/2010
Basically you make a synthetic key LeadId|LeadDate|OppDate, and put all combinations in one link table. The two fact tables cannot have any other field in common moving LeadId to the LinkTable. If you inclue LeadId as dimension facts referrring to the same LeadId will be grouped so you can do correct calculations on them. However this is the more complex solution, so building charts may become less trivial. This may not be what you are looking for. If you can do without, I'd take the second option.