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]

Hello again,

Here is a trimmed version of my application.

Thank you in advance,

Ibrahim

[Kushal_Chawda]

Hi there was a mistake in my script. '*' keyword was missing in calendar preceding load, due to which calendar and problem table was not linked on Date. Try below it should work

PrbCalendar:

LOAD *,

  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;

LOAD date($(vPROpenMinDate)+iterno()-1) as PROpenDate

autogenerate 1

While $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate);

[Anonymous]

This is the facts table. Is this normal?

The count of ID is 3 which is correct but one about the remaining NULL rows?

[Anonymous]

Hi again.. I also would like to clarify that the following expression is not counting the number correctly:

=Count( { $<PROpenDateMonthYear={"$(=Max( PROpenDateMonthYear ))"}> } ID )

It is showing 7 not 3.

[Kushal_Chawda]

In your calendar you are generating all the dates between min and max date. So any date which is not available in your Fact will be shown as NULL which is normal.

[Kushal_Chawda]

what are the values in PROpenDateMonthYear?

[Anonymous]

They are generated using your script above (here).

Here is a screenshot:

[Kushal_Chawda]

try this

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

[Anonymous]

It's returning 0. I have also tried this and it's still giving me 7:

=Count( { $<PROpenDateMonthYear={'Mar-2017'}> } ID )

[Kushal_Chawda]

Can you post your application?