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.

[Anonymous]

Here you go...

[Kushal_Chawda]

you had written the wrong expression

Wrong:

=Count({$ <PROpenDateMonthYear = {"$(=Date( Max( PROpenDateMonthYear ),'MMMM-YYYY'))"}>}ID)

This would have worked when MonthYear values were like January-2017 etc

Corrected:

=Count({$ <PROpenDateMonthYear = {"$(=Date( Max( PROpenDateMonthYear ),'MMM-YYYY'))"}>}ID)

You need to format as per the values, eg for Jan-2017 format should be MMM-YYYY

[Anonymous]

Yes, you are right but the results are still showing 7 while it should be 3 only.

[Kushal_Chawda]

use below expression

=Count(DISTINCT{$ <PROpenDateMonthYear = {"$(=Date(AddMonths(max(PROpenDateMonthYear),-1),'MMM-YYYY'))"}>}ID)

[Anonymous]

The result is 1 not 3.

[Kushal_Chawda]

I am getting 3. Are you using same expression, it is previous month expression

[Kushal_Chawda]

see attached

[Anonymous]

I think I made a mistake. I tried again and the counting is working. Thanks a lot 🙂

I'll do more testing and I'll let you know of the results.

[Kushal_Chawda]

Please mark the answer as correct if it is working fine to close the thread.

[Anonymous]

Sure.. I will try to do that by today 🙂

Thanks a lot!