Three dates convert into month

[#1]

Hi All,

I have a situation in which I have three dates; start date, end date, and cancel date. I have to combine these three dates and make them into one date called the desired date. Hopefully once I have this desired date I want to make a Month (desireddate) as desiredMonth so by clicking on a certain month; all the three dates will get filtered out in the back end for the feasibility of the business users. I am trying to achieve this by using master calendar but I'm finding it baffling how using the master calendar take me from three dates into one date so I can make a month out of that desired date.

Thanks,

[Anonymous]

The answer to this post may help with creating a solution:

Using Multiple Dates With Master Calendar

Essentially, you create a table that links all of your dates to your master calendar.

Hope that Helps!

Nate

[sathishkumar_go]

Hi.

All the 3 fields are I same table?

take

link:

keyfield,

d2.linecanceldate as Date,

Month(d2.linecanceldate) as Month,

'Cancel' as Type

from tablename;

concatenate (link)

keyfield,

lastbilldate as Date,

Month(d2.lastbilldate) as Month,

'End' as Type

from tablename;

concatenate (link)

keyfield,

linestartDate as Date,

Month(d2.linestartDate) as Month,

'Startl' as Type

from tablename;

--Sathish

[#4]

Yes all those three dates are in one table.

What do you mean by keyfield? Below please find my illustration of the description you provided. If I was to give all of them Date and Month alias wouldn't it give an error showing duplicate names in the same table.

link:

keyfield,

d2.linecanceldate as Date,

Month(d2.linecanceldate) as Month,

'Cancel' as Type

from contract;

concatenate (link)

keyfield,

lastbilldate as Date,

Month(d2.lastbilldate) as Month,

'End' as Type

from contract;

concatenate (link)

keyfield,

linestartDate as Date,

Month(d2.linestartDate) as Month,

'Start' as Type

from contract;

Thanks,

Arsalan

[MarcoWedel]

maybe helpful:

Canonical Date

regards

Marco

[avinashelite · Accepted solution]

you could create a link table for the same and then combine the new date with the master calendar

link:

keyfield,

d2.linecanceldate as Date,

'Cancel' as Type

from contract;

concatenate (link)

keyfield,

lastbilldate as Date,

'End' as Type

from contract;

concatenate (link)

keyfield,

linestartDate as Date,

'Start' as Type

from contract;

in your master calendar rename the Date column as the Date that should link you data with the master calendar like

Master_Calendar:

LOAD date as Date,

...

....

NOTE:Make sure your master calendar date format and the linking date format are all same