Calendar fields returning 0 when selecting. Do I need to floor my Date field as it has DateTime values?

[jblomqvist]

Hi all,

I am getting 0's when selecting values in fields such as Year, Month etc.

My Date field in the Facts table is a Date and Time field and contains date and time values.

Do I need to floor it?

I am using a standard master calendar script like this:

MinMax:

LOAD

    Min(Date) AS MinDate,

    Max(Date) AS MaxDate

RESIDENT Facts;

// ***** Set up the variables *****   

LET vMinDate = Num(PEEK('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(PEEK('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

// ***** Temporary Calendar *****

TempCal:

LOAD

    DATE($(vMinDate) + ROWNO()-1) AS TempDate

AUTOGENERATE

    $(vMaxDate) - $(vMinDate) +1;

DROP TABLE MinMax;

// ***** The Master Calendar *****

MasterCalendar:

LOAD

    TempDate AS Date,

    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,

    DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

    WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

    INYEARTODATE(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

    MonthStart(TempDate) as MonthStart

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[sunny_talwar]

I think that will be a good idea

Fact:

LOAD Date as TimeStamp,

          Date(Floor(Date)) as Date,

          .....

FROM ....;

MinMax:

LOAD Min(Date) AS MinDate,

    Max(Date) AS MaxDate

RESIDENT Facts;

// ***** Set up the variables ***** 

LET vMinDate = Num(PEEK('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(PEEK('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

// ***** Temporary Calendar *****

TempCal:

LOAD

    DATE($(vMinDate) + ROWNO()-1) AS TempDate

AUTOGENERATE

    $(vMaxDate) - $(vMinDate) +1;

DROP TABLE MinMax;

// ***** The Master Calendar *****

MasterCalendar:

LOAD

    TempDate AS Date,

    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,

    DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

    WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

    INYEARTODATE(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

    MonthStart(TempDate) as MonthStart

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[CathyRDuvall]

If flooring the DateTime does not work, you may need to change the number in the UI.  Before using the date in your application, right click on an empty spot on your sheet, click properties and select and date field.  After the date field is selected, Right click on the top caption and list box properties.  Override document settings, to change the number format to date with the needed Format Pattern that is already in your system.  See below: