Error in Expression when using MasterCalendar

[amber2000]

Hello everyone,

I'm strugling with my MasterCalendar, when I do a reload I'm getting :

Error in Espression ')' Expected

I can't find where the error occurs.

Does anybody know why please?

[jpenuliar]

You have ":" semicolon in the below:

RESIDENT TempCalendar: 

it should be

RESIDENT TempCalendar

[jpenuliar]

and the below lines:

InYearToDate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag
InYearToDate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

Try with If() function

[amber2000]

How do you mean with the If function?

[jpenuliar · Accepted solution]

or below:

YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

[MK_QSL]

MasterCalendar:

Load

  DATE#(TempDate, 'YYYYMMDD') as DT_Create_Dates,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  WeekDay(TempDate) as WeekDay,

  'Q' & CEIL (Month(TempDate)/3) AS Quarter, //Ceil = afronden Tempdate/3 van een non decimaal getal naar een geheel getal

  Date(MonthStart (TempDate), 'MMM-YYYY') as MonthYear, //For TrendInformation (alle dagen van de maand naar de 1e dag van de maand brengen anders heb je 31 x januari 2007)

  Week (TempDate) & '-' & Year(TempDate) as WeekYear,   //For TrendInformation

  InYearToDate(TempDate, Today(), 0) * -1 AS CurYTDFlag,

  InYearToDate(TempDate, Today(), -1) * -1 AS LastYTDFlag

  // Herladen van de tabel TempCalendar, als we dit niet doen moeten we voor elke Datefield 'Month', 'Year' , 'Week' etc. de volledige loop gebruiken van MinMaxTemp tabel

  RESIDENT TempCalendar

  Order by TempDate ASC;

[amber2000]

Thanks Jonathan, your last remark did the trick

[amber2000]

Jonathan,

After testing I see that my Month is not linked to SD_Close_Date.

If I have no selection I get my data, if I make the selection for month 'mrt' then there is no data displayed.

Somewhere there is still something wrong, can you see where?

Kind regards,

Ambertje

[jpenuliar]

Hi Amber,

SD_Close-Date is not included in your Fact table, see below:

[jpenuliar]

The idea of the Master Calendar is to link it to your Date field.

In your case, you have the Master Calendar linked to DT_Create_Dates.

You will have to make another Calendar Table to link to SD_Close_Date.

another way is to look into Common Date technique or Canonical Calendar, quick search in the community will do