Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Issues with importing date from excel ad formatting to monthyear

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

11 Replies
MVP
MVP

Re: Issues with importing date from excel ad formatting to monthyear

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...

Not applicable

Re: Issues with importing date from excel ad formatting to monthyear

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

Not applicable

Re: Issues with importing date from excel ad formatting to monthyear

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

MVP
MVP

Re: Issues with importing date from excel ad formatting to 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?

Not applicable

Re: Issues with importing date from excel ad formatting to monthyear

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

MVP
MVP

Re: Issues with importing date from excel ad formatting to monthyear

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?



Re: Issues with importing date from excel ad formatting to monthyear

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

Not applicable

Re: Issues with importing date from excel ad formatting to monthyear

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

Not applicable

Re: Issues with importing date from excel ad formatting to monthyear

I have by the way solved this issue.

The problem was how I recieved the data from excel