topic Re: Send to Excel converts date to text in QlikView

Send to Excel converts date to text

Anonymous — Wed, 10 Aug 2011 07:57:55 GMT

Hi,

I am using monthname(datefield) to display the month and year as a dimension in a chart and then using sort by numerical value to put these in date order. We recently upgraded our server and clients to V10 SR3 and since then when pressing teh 'Send To Excel' icon in the caption the date dimensino comes out as text in Excel. Before the upgrade it used to come through as a number (the excel internal date). Any idea on how I can get V10 to produce dates/numbers on the send to excel again?

Thanks

Re: Send to Excel converts date to text

Wed, 10 Aug 2011 09:49:42 GMT

Hi,

Please set the number format of the field in the QVW document after reload from

Menu - > Settings - > Document Properties - > Number tab

Select the Month Name field from the fields listed in the Number tab and set the Number formatting as per the attached snapshot.

Cheers.

Re: Send to Excel converts date to text

Anonymous — Wed, 10 Aug 2011 10:37:04 GMT

Unfortunately that doesn't work I'm afraid. The data in Excel is still coming through as text.

Re: Send to Excel converts date to text

Thu, 11 Aug 2011 01:29:34 GMT

Hi,

Can you please attach the test app you are trying, you can reduce the data by making some selections and choose from Menu -> File -> Reduce Data -> Keep Possible values and save it as another copy and send us.

Cheers.

Re: Send to Excel converts date to text

Anonymous — Thu, 11 Aug 2011 09:16:08 GMT

I've attached a file for you to look at and have also done some testing. It seems to be when I have multiple dimesions in a chart as you will see in the attached. The top chart has one dimension and the send to excel keeps the date format correctly. The 2nd chart adds another dimension and the send to excel then comes through as text.

Peter

Re: Send to Excel converts date to text

its_anandrjs — Thu, 11 Aug 2011 09:40:33 GMT

Hi driscolp,

You need to add Month(EpiEDateGrph) in the Dimension and then try to export as a excel you see the month name comes up with the number values like 1,2,3,4 for Jan,Feb,Mar,Apr thats you required. or if you want year should be also display so you need to change in your load script as a MonthName(EpiEDateGrph)

Hope this helps you.

Regards,

Anand

Re: Send to Excel converts date to text

Anonymous — Thu, 11 Aug 2011 09:53:22 GMT

I'm already using Monthname(EpiEDate) as EpiEDateGrph in the script to convert a date (EpiEDate) to the Month Year format.

Re: Send to Excel converts date to text

Thu, 11 Aug 2011 09:58:09 GMT

Hi,

Really strange why it exports as number instead of month name when you add one more dimension.

But i given a try but it is not perfect see if it can help you. After export it look like text but when we double click on the cell it will be changed as date in excel after export.

Please see the attached sample.

Re: Send to Excel converts date to text

Anonymous — Thu, 11 Aug 2011 11:12:44 GMT

Thanks, it looks like my chart does the same thing. It's not ideal for a user though as the number of items in that dimension could be rather large and we can't have them having to double click on each one in Excel. I may escalate this further as a bug fix.

Re: Send to Excel converts date to text

Fri, 12 Aug 2011 03:52:15 GMT

Hi,

As an alternate you can give a button to the users to export the graph or table to excel on click of a button, on click of the button run macro wich willexport data to excel and do the formatting.

Please see the attached sample which i donwloaded from community some time back.

Cheers.

Re: Send to Excel converts date to text

Anonymous — Fri, 12 Aug 2011 07:30:24 GMT

Thanks, may consider that as an option.