Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
hammermill21
Creator III
Creator III

Date Issue Reading Date Format

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?

Capture.PNG

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

20 Replies
sunny_talwar

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

hammermill21
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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

Capture.PNG

Step 2)

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

[Username] AS [Bascom Username],

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

Master calendar is good

hammermill21
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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?