Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am importing a completion date with the following format but i want to group by on mmm-yy
how can i convert this date field - I have tried date(completiondate,'MMM-YY) but it displays nothing - just dash "-"
Is it better to load the dates with the month year or to format them after the load
The purpose is to build a spreadsheet with 12 months of data per month for completion dates
Thanks
Rick
Please see attachment
You missed a ' in your expression - or is that just mistyping when you wrote the question?
If not - then make sure that completiondate is actually a real date for QlikView by looking at what Num(completiondate) looks like. That will give you a number in 40000's with possibly some fraction.... then it is a real date to QlikView...
Please review the attachment as I have displayed the number format of the date as 35,571. I still cannot convert to the a month date format?
Thanks
when I user num(completiondate) - i get nothing - so its not a number.
now do i use the date# to convert it into a number and then a date format to get the monthyear
Rick
If you try to do =Date(35571) you will get 21st of May 1997 as a date ...
If you do =Timestamp(35571.89) you will get 21st of May 1997 09:21:36 PM
How about the , is it a thousand separator in your language setup?
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
i believe its a string - so how would i convert it to a date and then a month year?
Thanks
Just to be sure I help you with the right problem .... Where do get the dash - as the result from this expression:
date(completiondate,'MMM-YY)
Is it in a Text Box, a Chart or where?
If you use the expression without being wrapped in an aggregate function as expression in a chart you will get a
dash if not a single value is picked out with the dimensions in the chart... Could that be the case?
Hi,
you could try
Date#(completiondate,'MMM-YY')
instead of
Date(completiondate,'MMM-YY')
as
Date# interprets a string as date, while
Date only formats an already numerical value in the date format specified.
hope this helps
regards
Marco
Hej der,
I have in my qvw file this settings which are ok
SET DateFormat='DD-MM-YYYY';
But when I import dato from excel i get something like 41306.000000
If i only got the 41360 then qlikview would see it as a date, but it does not because of the .000000
Excel format is date but it sends it with extra ceros.
What can I do to cut the . and the ceros
Thanks in advance
Martha
I have by the way solved this issue.
The problem was how I recieved the data from excel