topic Re: Why it is adding a day to the date data when exported into excel in QV V10 R2 in QlikView

Why it is adding a day to the date data when exported into excel in QV V10 R2

Fri, 15 Jul 2011 15:45:12 GMT

Hi,

Has any one encounter this issue and is there something it can be done to prevente it?

When data is exported to Excel, the date is changed - it becomes a day later. For Example, the data below in QlikView V10 R2 shows dates of 7/3/2011 and 7/10/2011 and when exported to excel it shows 7/4/2011 and 7/11/2011 respectively

Transfer to Excel the date changes to a day later...

THD Year	THD Month	Week End Date	Item name	Item number
2011	Jul 2011	7/4/2011	8x205x1210 THD Cherry ST54 AC3	367211-00099
2011	Jul 2011	7/11/2011	8x205x1210 THD Cherry ST54 AC3	367211-00099
2011	Jul 2011	Total
2011	Total

Thanks for your help.

Miguel Mena

This is an Scramble sample:

Why it is adding a day to the date data when exported into excel in QV V10 R2

m_woolf — Fri, 15 Jul 2011 16:59:13 GMT

Maybe you could attach a sample qvw file that demonstrates the problem.

Re: Why it is adding a day to the date data when exported into excel in QV V10 R2

Mon, 18 Jul 2011 12:24:31 GMT

Hi,

I just posted an scramble sample. Thanks.

I noticed that the behavior it is only occurring on Version 10 and not

with version 9

Miguel Mena | Business Intelligence Specialist

Clarion Industries | Clarion Laminates, LLC | Clarion Boards, Inc

919-653-7886 (p) | 919-653-6054 (f) | 919-609-9229 (c)

Miguel.Mena@ClarionIndustries.com

<mailto:Miguel.Mena@ClarionIndustries.com

Re: Why it is adding a day to the date data when exported into excel in QV V10 R2

m_woolf — Mon, 18 Jul 2011 14:13:49 GMT

I see the same behavior in ver 9 SR6.

When I Send to Excel, set the format to general, and increase the number of decimal places I get this:

Week End Date	Cartons Sold	Cartons on Hand
40671.99999998840	-41,396	245,967
40678.99999998840	-41,017	246,918
40685.99999998840	-41,144	247,019
40692.99999998840	-40,866	246,313
40699.99999998840	-41,733	246,779
40706.99999998840	-41,551	248,388
40713.99999998840	-41,412	247,980
40720.99999998840	-40,777	245,063
40727.99999998840	-40,281	242,752
40734.99999998840	-41,101	246,484

I suspect the QlikView is showing the first date as 40671 and Excel is rounding to 40672.

Maybe you could use date(floor(DDWeekendDate)) to remove the decimals?

Re: Why it is adding a day to the date data when exported into excel in QV V10 R2

Mon, 18 Jul 2011 20:36:50 GMT

Thank you! It worked...