Master Calendar Not Reading Date Properly

[Anonymous]

Hi,

Using the Master Calendar to load the date field in the form of Day, Week, Year, etc shows a dash in some records while if I create an additional dimension for that record using the Day(), Week(), or Year(), then it works. Is there something wrong in my script?

LOAD
    "OPEN_TIME" as PROpenTime,
    Date( Floor( "OPEN_TIME" ) ) as PROpenDate;
SQL SELECT * FROM databasename;

// The Master Calendar
Load
     PROpenDate,
     week( PROpenDate ) As PROpenDateWeek,
     Year( PROpenDate ) As PROpenDateYear,
     Month( PROpenDate ) As PROpenDateMonth,
     Day( PROpenDate ) As PROpenDateDay,
     YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
     YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
     date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
     ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
     Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
     WeekDay( PROpenDate ) as PROpenDateWeekDay
Resident themaintable;

Here is a sample of the outcome:

You will notice that the second record is working properly while the first one is not.

Thank you for your response in advance.

[ahaahaaha]

Hi,

May be in 12 line

Day(Date#(PROpenDate, 'DD/MM/YYYY')) As PROpenDateDay,

[Anonymous]

Nope.. It's giving me the same results .

[vikasmahajan]

Try this

Day(Date (Num(Evaluate(DATE) ))  ) As PROpenDateDay,

Vikas

[Anonymous]

I think you meant to say:

!Day( Date( Num( Evaluate( PROpenDateDay ) ) ) ) As PROpenDateDay,

Still no luck.. in fact it gave me wrong results even for the correct record, the PROpenDateDay value showed 30!

[Anonymous]

Sorry here is the correct statement but still no luck:

Day( Date( Num( Evaluate( PROpenDate ) ) ) ) As PROpenDateDay,

[Kushal_Chawda]

try this

Day(trim(PROpenDate)) As PROpenDateDay,

[Anonymous]

Not working.. it's giving me the same outcome from my original post.

[ashok1203]

Can you try this

Day( Num#(PROpenDate) ) As PROpenDateDay,

[Kushal_Chawda]

try creating it in below load statement and then call it in calendar

LOAD 

"OPEN_TIME" as PROpenTime, 

Date( Floor( "OPEN_TIME" ) ) as PROpenDate,

Day( Floor( "OPEN_TIME" ) ) as PROpenDateDay;

SQL SELECT * FROM databasename;