Generation of missing dates across common axis for multiple date fields

[Anonymous]

I am using a combination of Canonical Calendar and Generating missing reference dates concept .

we have multiple systems migrated into one new system and I need to show all dates on one time axis.

eg. [Opened Date], [Resolution Date] from New system

[Date Raised],[Resolved Date] from TR System

[Open date UTC],[Resolve date UTC] from IN system

I need to show a trend of outstanding tickets across one common month axis.

Outstanding ticket count as of 28 Feb = all tickets with blank [Resolution Date],[Resolved Date] and [Resolve date UTC] while being opened in Feb or prior to Feb. +

Tickets that were opened in Feb or prior to Feb but resolved after 28 Feb.

I am able to generate missing dates but only for one date field.

Challenge that I am facing is that even if I generate missing dates for each of these date fields how do i show it all on one axis.

[jonathandienst · Accepted solution]

If the Opened Date, Date Raised and Open date UTC all mean the same thing, then I would alias them so the name is the same:

     LOAD

          ...

          [Opened Date]

     LOAD

          ...

          [Date Raised] as [Opened Date],

     etc

(This is assuming, of course, that you are concatenating the facts from all three sources into a single fact table)

[Anonymous]

Thanks Jonathan.. This helped and it did work out though script takes a longer time to load