Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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
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.
BI Consultant
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
Hi
Use
text(Month(date))
Hello Swapnil,
That fixes the export issue, but then the data isn't sorted by Month name.
Kind Regards,
Dayna
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...
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,
BI Consultant
I thought as much, could you get the sorting to work if the dimension was text() ?