topic Re: Date format Of Chart getting screwed while sending to excel in QlikView

Date format Of Chart getting screwed while sending to excel

ektarathi — Fri, 14 Sep 2012 17:42:00 GMT

Hi All,

I have a Bar Chart in which Dimension is a "Date".The dimension axis shows the correct value in the graph.

But when I export the Graph to excel, the date gets converted to a number.To see the correct date then, I have to format the cells of excel

to "date".Can I somehow fix this in Qlikview.

The expression that I am using in Dimension is :

=If([As of Date]=AsOfDateVar or [As of Date]=YearVar1 or [As of Date]=YearVar2 or

[As of Date]=YearVar3,

If((([TrendTypeVar]='Total' and [EEOHIST_MMM_EEOX_YR_RECD]='SY') OR

([TrendTypeVar]='Demotion' and ([EEOHIST_MMM_EEOX_YR_RECD]='DN'OR [EEOHIST_MMM_EEOX_YR_RECD]='DY')) OR

([TrendTypeVar]='Hire-Rehire' and ([EEOHIST_MMM_EEOX_YR_RECD]='H'OR [EEOHIST_MMM_EEOX_YR_RECD]='R')) OR

([TrendTypeVar]='Lateral' and [EEOHIST_MMM_EEOX_YR_RECD]='E') OR

([TrendTypeVar]='Layoff' and [EEOHIST_MMM_EEOX_YR_RECD]='L') OR

([TrendTypeVar]='Promotion' and ([EEOHIST_MMM_EEOX_YR_RECD]='PN'OR [EEOHIST_MMM_EEOX_YR_RECD]='PY')) OR

([TrendTypeVar]='Recall' and [EEOHIST_MMM_EEOX_YR_RECD]='C') OR

([TrendTypeVar]='Termination' and [EEOHIST_MMM_EEOX_YR_RECD]='T') OR

([TrendTypeVar]='Transfer In' and [EEOHIST_MMM_EEOX_YR_RECD]='I') OR

([TrendTypeVar]='Transfer Out' and [EEOHIST_MMM_EEOX_YR_RECD]='O'))

,date([As of Date],'MMM yyyy')))

[As of Date] field is coming from database and is in date format there.

Any suggestions??

Re: Date format Of Chart getting screwed while sending to excel

Nicole-Smith — Fri, 14 Sep 2012 17:49:53 GMT

Did you try setting the format to Date on the Number tab?

Re: Date format Of Chart getting screwed while sending to excel

ektarathi — Fri, 14 Sep 2012 18:50:45 GMT

I did not get you??"number tab" means??

Re: Date format Of Chart getting screwed while sending to excel

ektarathi — Fri, 14 Sep 2012 19:09:56 GMT

ohh..I did not understand that you are talking about that tab.But that tab only allows you to format expression(Y-axis) in chart and not dimension i guess.But In my chart the dimension(X- axis) is causing problem.

Can I format dimension from some place?

Re: Date format Of Chart getting screwed while sending to excel

Anonymous — Fri, 14 Sep 2012 19:10:31 GMT

Try to add text():

,text(date([As of Date],'MMM yyyy'))))

Re: Date format Of Chart getting screwed while sending to excel

ektarathi — Fri, 14 Sep 2012 19:14:02 GMT

Hey...thanks a lot!!!That solved the issue!!!!