Date Issue Reading Date Format

[hammermill21]

Hello,

I have dates that are 01/10/2018 and 1/12/2018 so depending on what selection I make it's not reading both. How can I fix that?

Thank you!

[sunny_talwar]

Not sure I understand your issue? You have two different formats? How do they differ and what are you expecting?

[hammermill21]

Hey Sunny!

Some of my dates are 01/10/2018 the month is MM format then some are 1/10/2018 so the Month is M format. I need them all to be in MM/DD/YYYY format and I'm not sure what that is happening since the data I'm pulling is in MM/DD/YYY format.

I'm not sure if it's something that is wrong with my Master Calendar:

MinMaxDate:

Load

min([Get Date]) as MinDate,

    max([Get Date]) as MaxDate

resident [root];

Let vMinDate = Peek('MinDate',0,'MinMaxDate'); 

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Let vToday = $(vMaxDate);

drop table MinMaxDate;

TempCal:

Load

date($(vMinDate) + rowno()-1) as TempDate

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

Mastercalendar:

Load

TempDate as [Get Date],

    year(TempDate) as Year, //this is so you can select by year

    month(TempDate) as Month, //this is so you can select by month and so on

    monthname(TempDate) as MonthYear,

    day(TempDate) as Day,

    weekday (TempDate) as WeekDay,

    week(TempDate) as Week,

    year(yearstart(TempDate,0,4)) as FiscalYear,    // means starting same time every year but the 4th month

'Q' & ceil(month(TempDate)/3) as Quarter,      //ceil means to round up; 'Q" & is so you can have Q1, Q2..

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

[sunny_talwar · Accepted solution]

Check/Modify two things

1) Your environmental variables have this

SET DateFormat='MM/DD/YYYY';

2) Make sure to do this in your fact table

Date([Get Date]) as [Get Date]

and in your master calendar

Date(TempDate) as [Get Date],

By doing this, you will pick the Date format from Environmental variable and will have it consistently the same across each cell

[hammermill21]

Let me make sure I understand:

In my Main table I have this:

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

[Inspection Date] AS [Get Date],

Then in my Master Calendar, I have this:

Mastercalendar:

Load

     Date(TempDate) as [Get Date],

    year(TempDate) as Year,

    month(TempDate) as Month,

    monthname(TempDate) as MonthYear,

    day(TempDate) as Day,

    weekday (TempDate) as WeekDay,

    week(TempDate) as Week,

    year(yearstart(TempDate,0,4)) as FiscalYear,  

'Q' & ceil(month(TempDate)/3) as Quarter,    

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

resident TempCal;

drop table TempCal;

Is that what you mean?

Thank you Sunny for your help as always.

[sunny_talwar]

Step 1) make sure your environmental variable to have the right date format

Step 2)

LOAD TEXT ([Barcode]) AS [Bascom Barcode],

[Username] AS [Bascom Username],

Date([Inspection Date]) AS [Get Date],

Master calendar is good

[hammermill21]

Ah! I missed the "environmental variable" part and I had tried the Date([Inspection Date]) AS [Get Date] but got an error and thought it was wrong. It's that my environmental value was off.

This helps a lot, thank you

[sunny_talwar]

Super

[hammermill21]

Another question regarding the Dates... Sorry everytime I think I got it, I don't! lol

So now that I have my date working perfectly when I add a filter by "Month" it's not calculating correctly. Yet if I select all the individual dates my numbers are right. Does that have to do with my master calendar? Should I be adding something in my script to format the Month differently?

[sunny_talwar]

So now that I have my date working perfectly when I add a filter by "Month" it's not calculating correctly.

Not calculating correctly? You will have to elaborate what is going on?