When pulling together data from multiple sources, you’ll have a number of different dates. For instance, the marketing department has a Campaign Date, Sales department may have an Opportunity Date and Finance has a GL Date.
Modeling Challenge
This type of data leaves us with three sets of dates. Modeling it properly will allow you to select from one common date field while still leaving you the ability to choose from one of the three date fields individually.
Solution
We need to come up with a Custom SQL Expression that retrieves a unique list of the dates used between the three tables.
We create a Custom SQL Expression with the following Syntax:
Select Distinct x.[Common Date] from (
Select [GL Date] AS [Common Date]
FROM [GL Entries]
Union All
Select [Marketing Campaign Date] AS [Common Date]
FROM [Marketing Campaigns]
Union All
Select [Opportunity Date] AS [Common Date]
FROM [Sales Opportunities]
) as x
Lastly, we link the four tables together:
Results
This gives us the common date field to use. This will allow us, for example, to choose a month and it will narrow down the selections across the three tables.
