Calendar issue

[#1]

hi all i've created a test app, i've absolutely no idea what can be happening.

i've tryed all with date(date# timestamp and so on, but cant link my fact table to the master calendar. That master calendar is working on other apps i've seen without any issue.

Find attached the qliksense app. If i do Day(create_time) as daytest it works and it shows the day. once i do lync create_time to the master calendar... there is no way to make it work...

Could anyone help?

huge thanks!!

DATA

[calendarissue]:

LOAD [CodiID],

  [code],

  [create_time],

  [daytest]

FROM [lib://C/calendarissue.qvd]

(qvd);

CALENDAR

// Calendar

LET vDateMin = Num(MakeDate(2014,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date(($(vDateMin) + RowNo() - 1),'DD/MM/YYYY') AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS create_time,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

//Month(TempDate) AS Mes,

//'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'Q' & Ceil(Month(TempDate)/3) AS Trimestre,

//Year(TempDate) AS CalendarYear,

Year(TempDate) AS Año,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Dates

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[Gysbert_Wassenaar]

Try cutting of the time part of the create_time field. Your calendar dates won't have time fractions.

[calendarissue]:

LOAD [CodiID],

  [code],

  Floor([create_time]) as create_time,

  [daytest]

FROM [lib://C/calendarissue.qvd]

(qvd);

[effinty2112]

Hi Kilian,

Try this:

Add this above your calendarissue load statement to add a create_day field in that table:

Load

*,

Date(Floor(create_time)) as create_day;

LOAD CodiID,

....

In your calendar load statement replace the line

Date(TempDate) AS create_time

with

Date(Floor(TempDate)) as create_day;

Hope this helps

Andrew