topic Date changes to Number when export to Excel in QlikView

Date changes to Number when export to Excel

Wed, 06 Jul 2011 22:08:53 GMT

I've following code that generates a Calendar QVD. That QVD is then referred by other QV applications as input. Chart within QV displays date/months/quarters/years as desired format. However, when the values in chart is exported to excel, date format changes to numbers.

start month = 40694

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths(StartMonth,-i))));
let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths(StartMonth,-i))));

MasterCalendar:
load $(vLoop_Month) as DateKey,
     Month($(vLoop_Month)) as CM,
     'Q'&ceil(Month($(vLoop_Month))/3) as CQ,
     Year($(vLoop_Month)) as CY,
     dual(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3),$(vLoop_Quarter)) as CYQ,
     dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)) as CYM,
     Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,
     dual('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY'),$(vLoop_Quarter)) as CYQS
autogenerate(1);

Store above into Calendar.qvd

Other QVWs just reads the Calendar QVD. So all the fields such as CM, CQ, CY, CYQ, CYM, CYM2, and CYQS are displayed as desired date format in QV. However, when I export them to excel they are converted to numbers. Any help will be appreciated. Thanks

Re: Date changes to Number when export to Excel

nagaiank — Wed, 06 Jul 2011 22:58:08 GMT

Check if the added field 'ExtDate' appears in Excel in the date format.

StartMonth = 40694;

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths($(StartMonth),-i))));

let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths($(StartMonth),-i))));

MasterCalendar:

load $(vLoop_Month) as DateKey,

Date($(vLoop_Month),'MM/DD/YYYY') as ExtDate,

Month($(vLoop_Month)) as CM,

'Q'&ceil(Month($(vLoop_Month))/3) as CQ,

Year($(vLoop_Month)) as CY,

dual(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3),$(vLoop_Quarter)) as CYQ,

dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)) as CYM,

Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,

dual('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY'),$(vLoop_Quarter)) as CYQS

autogenerate(1);

Next

Date changes to Number when export to Excel

Fri, 15 Jul 2011 21:37:08 GMT

Go to "User Preferences" --> "Export Tab" select "Full formatting" and see if that helps .....

Date changes to Number when export to Excel

Fri, 15 Jul 2011 23:54:52 GMT

Hi there,

The added field appears as date, but I cannot use that for Quarters and other formats. What will be the date format for CQ, CYQ or CYQS in the above code? The other thing I've tried is converting everything other field, except the date field to text. This time it exports to excel in the desired format, but sorting in the charts within QV is messed up. For some reason I can't get QV and Excel to work at the same time. Thanks for your help.

Re: Date changes to Number when export to Excel

nagaiank — Sat, 16 Jul 2011 06:03:33 GMT

Try the following script and it works for me.

StartMonth = 40694;

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths($(StartMonth),-i))));

let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths($(StartMonth),-i))));

MasterCalendar:

load $(vLoop_Month) as DateKey,

Date($(vLoop_Month),'MM/DD/YYYY') as ExtDate,

Month($(vLoop_Month)) as CM,

'Q'&ceil(Month($(vLoop_Month))/3) as CQ,

Year($(vLoop_Month)) as CY,

Text(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3)) as CYQ,

Text(Year($(vLoop_Month))&'-'&Month($(vLoop_Month))) as CYM,

Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,

Text('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY')) as CYQS

autogenerate(1);

Date changes to Number when export to Excel

Thu, 20 Oct 2011 21:48:22 GMT

Property of line chart prevents me from exporting custom dates as dates and it converts them to number. Once I converted the line chart into Straight table and then exported, it started working as expected. So from now on every chart that needs exporting we ended up giving Fast Change option to clients.