Representing a date as a month - but keep formatting when exported to excel

[Dayna]

Hello All,

I don't know if its the version of Excel we are running, but on one of my QlikView reports when you export the data the month comes out as 1,2,3.. etc.. Rather than Jan, Feb. 

My expression is simply =month(date), what I found, is that I can do a =text(month(date)) and it'll keep the representation when I export it - but then the data isn't sorted correctly, so rather in ascending month (Jan-Dec) it's all over the place.

Unfortunately I can't do this in my load script, as one is slightly more complex, so I have to do it in my expression.

Has anyone got any ideas?

Many thanks for your help!

Kind Regards,

Dayna

[Miguel_Angel_Baeyens]

Hi Dayna,

You can always use the Sort tab in the chart properties and set an expression (Month number field) and in the Expressions tab set the Text instead, so the export is done according to your sort criteria.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Dayna]

Hello Miguel,

Many thanks for your reponse, but it still doesn't work my month field is a dimension in a pivot table if this makes a difference?

My calculated dimension is now =text(month(Date)) so the export format works, but the sorting doesn't...

I have within my Sort tab for the Month dimension month(Date). (it's at the bottom of 4 dimensions, but these don't have any sorting - and I can't promote it as it's in a pivot).

Kind Regards,
Dayna

[Miguel_Angel_Baeyens]

Hi Dayna,

The application I attached uses a pivot table, and the export works just fine with the model provided (Excel 2007). Besides, I'm using the Sort expression for the first dimension, and in any case, the Month column is sorted numeric ascending, which should do as well. Please post some sample data to check your specific case.

I have edited the previous post and reattached pivot table with sort criteria and so.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Dayna]

I think the format issue is caused by the version of Excel, as it'll work for me on some versions but not others.. But if you assume the export would only work using the text() function.

Please find attached an example, the top table is a simple month() display, the second one takes into consideration calendar months..

Many thanks for your help.

Dayna

[#6]

Hi

Use

text(Month(date))

[Dayna]

Hello Swapnil,

That fixes the export issue, but then the data isn't sorted by Month name.

Kind Regards,

Dayna

[#8]

Hello Dyana,

U can take another field  as Month(date) for sorting purpose.

Try to hide this new field.

I don't know it is possible or not...

[Miguel_Angel_Baeyens]

Hi Dayna,

Definitely is something related to Excel. I've just opened your file and clicked on the "XL" icon and it showed up with months in Text and sorted as they are in the chart. For what it's worth I'm using QlikView 10 SR 3 Build 9061 and Excel 2007 on Windows 7 64 bit.

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Dayna]

I thought as much, could you get the sorting to work if the dimension was text() ?