
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Qlil Sense Date Query
Hi All,
I have a query regarding how Qlik sense processes dates in excel and what I can do at the preparatory stage to ensure that certain fields can be transformed from 'general' to 'date' format when I click 'edit this table' and amend a 'field menu' before loading data.
The dates in my excel files all follow the DD/MM/YYYY format and I double check to see if the format in excel is 'date'. Sometimes I am lucky and I can convert my dates into 'date' format inside qlik before uploading data. This then allows me to analyse dates by day, month, quarter, monthyear, year etc...automatically. But in most cases, when I change the field format from general to date all the data disappears as shown below:
When the field is still in 'General' format the data is in number form (42036 etc...)
When the field changed to 'date' format, the data disappears (keeping in mind that in the excel data source all dates are written dd/mm/yyyyy).
I realise there are Master calender functions that can be added to the data load editor after the data is uploaded, but I usually have problems inserting that script and if there was a way to allow me to change field format prior to uploading data (or at least know why sometimes i can change format from general to date and others times not) that would save a lot of time.
Thank you very much for your help!
Best,
Mohammed
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Nicolo,
Thank you for your support. In the end this worked:
Temp:
Load num#([Date Field],'#') as NumericDate
RESIDENT [TABLE];
MinMax:
LOAD Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
RESIDENT Temp;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD Distinct
text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date Field],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
DROP TABLE Temp;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mohammed,
I think you should go to the script and change
Date(Date#(DateField,'DD/MM/YYYY')) as DateField
That will automatically force Qlik Sense to interpret your field as a date field every time you'll load new data.
Let me know if I understood

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Nicolo,
Thank you for reply! I tried you script but can't seem to get it working. Not sure if I am placing it in the wrong place or if I'm losing it from trying so many things! haha
I have also tried the following which incorporates your expression:
1)
MinMax:
LOAD Min(MonthStart(MonthYear)) AS MinDate,
Max(MonthStart(MonthYear)) AS MaxDate
RESIDENT [TABLE NAME];
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD Distinct
Year(ReportDate) AS Year,
Month(ReportDate) AS Month,
Date(ReportDate,'MM-YYYY') AS MonthYear;
LOAD
Date(MonthStart($(vMinDate) + RecNo() - 1)) AS ReportDate
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
Exit Script;
2)
LOAD [Table Name],
Date(MonthStart(Num#([Date Field])), 'MM/YYYY') as MonthYear,
Year(num#(Date Field,'#')) as Year,
Month(num#(Date Field,'#')) as Month
Resident [Table Name];
DROP Table [Table Name];
and none of them worked or only partly worked. I realise I need to take a training on date functions though. The only place I can get your expression to work is as a master item in the UI. That allows me to view the information as DD/MM/YYYY then I need to put other expressions to view as MonthYear, Month, Year etc...
But is there away of avoiding changing script in the data load editor or having to make master items by addressing the issue in the preparatory stage before loading data? As per the screen shots I attached you can change field type from general to date and sometimes Qlik recognises it and changes everything to date format (and auto generates a master calender function for you), and other times it just gives you blank cells like in the second picture.
Best,
Mohammed

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mohammed,
could you provide us a sample of the result that you are getting with the above expressions?
I saw you used Date(ReportDate,'MM-YYYY') AS MonthYear, if you try with Date(ReportDate, 'DD/MM/YYYY'), do you still have errors?
Nicolò

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Nicolo,
Thank you for your support. In the end this worked:
Temp:
Load num#([Date Field],'#') as NumericDate
RESIDENT [TABLE];
MinMax:
LOAD Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
RESIDENT Temp;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD Distinct
text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date Field],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
DROP TABLE Temp;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mohammed,
I'm glad you found a solution at the end
Best regards,
Nicolò
