Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I'm new within QlikView and are testing the application.
I have tried to load with the following script
...................
SET DateFormat='MMM DD, YYYY';
...................
LOAD [No. (Ticket)],
[Department (resp.)],
[Created Date],
Year([Created Date]) as Year,
Month([Created Date]) as Month,
...................
my .xls file contain the coulmb Create Date e.g with the following date Jul 22, 2015
When I reload my data all goes well but the month and year are always empty... why is that?
Another question, is there a Quarter() function as well so I can convert a date to a quarter? I'm not able to locate it?
Thanks in advance
Peter
The reason will be that your [Created Date] won't be recognized as date. You need some convert- and fromat-functions for this, try:
year(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY')) as Year
- Marcus
The reason will be that your [Created Date] won't be recognized as date. You need some convert- and fromat-functions for this, try:
year(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY')) as Year
- Marcus
for the year see Marcus answer
for the quarter use
'Q' & ceil(month(Created Date) / 3)) AS Quarter,
for quarter, when you a have a date field, you can use
'Q' & Ceil(Month([Created Date])/3) as Quarter
Hi Marcus,
It works, thanks.
Could you shortly explain me how it works / how I should read it?
Thanks in advance,
Peter
Hi Rudolf and Massimo
It seems that your proposal also works or partly because I only get "Q" as a result.. I was expecting Q1, Q2, Q3 and Q4 as result.
Could you shortly explain me how it works / how I should read it and what the reason may be that I dont get the expected result.
Thanks in advance,
Peter
Hi again,
Found the problem, I ofcause mneed to take the input from Marcus as well into the script
'Q' & Ceil(Month(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY'))/3) as Quarter,
Best regards
Peter
Hi Peter,
a date is in real a number, starting with 1 on 01.01.1900 and times are parts from 1, for example 18:00 / 24 h = 0,75. QlikView will interprets date- and time-fields in consideration to the SET variables at the beginning from each script or if they are missing defined through the regional-settings from OS.
If you now loads date-fields with another formatting you need to tell qlikview thats are dates and how it should regocnize them. This will be done per date#(value, format) which returned if the formatting is correct a numeric value which then could be formated in any way you like - this field is then a dual-field with a string-representation from your choosen format and contains as numeric value the day-number counted up from 01.01.1900.
Many more information abouts dates could you find here: How to use - Master-Calendar and Date-Values
- Marcus