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.
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.
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]
Select [Marketing Campaign Date] AS [Common Date]
FROM [Marketing Campaigns]
Select [Opportunity Date] AS [Common Date]
FROM [Sales Opportunities]
) as x
Lastly, we link the four tables together:
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.