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
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?
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
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 TimeFormat='h:mm:ss TT';
i believe its a string - so how would i convert it to a date and then a month year?
Just to be sure I help you with the right problem .... Where do get the dash - as the result from this expression:
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?
you could try
Date# interprets a string as date, while
Date only formats an already numerical value in the date format specified.
hope this helps
I have in my qvw file this settings which are ok
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