topic Re: Export to excel: how do I get values as numbers instead of text? in App Development

Export to excel: how do I get values as numbers instead of text?

fukicubiq — Thu, 21 Dec 2017 09:30:30 GMT

Hi,

This probably applies both to QlikView and Qlik Sense: I have noticed that sometimes number values are exported as text when exporting a table from Qlik to Excel. This is of course quite inconvenient as it requires extra conversion steps in Excel to be able to do calculations.

I think this happens mainly when several different number formats are used within the same measure, e.g. in an income statement where some rows are € values and others are % values, or mixing € and $ values on different rows as per attached example.

I think in QlikView there is a hidden option to always force exported values as numbers, but does similar exist in Qlik Sense? Of course it would be preferable to get number values with same formatting as in Sense but if that is not possible then just numbers without any formatting would be better than getting the exported values as text. I already tried to implement the measure as calculated dimension but that produces text values in export as well.

Here is an example of how it looks like in Sense and as exported to Excel:

Attached is the qvf and excel file displaying this behaviour.

Thanks in advance!

Phuoc

Re: Export to excel: how do I get values as numbers instead of text?

MK9885 — Thu, 21 Dec 2017 19:09:54 GMT

May be change the formatting from Auto to Number as show in below image.... or you can change it to money as well...

Export it, check Format in XL, it will show as Number format instead of General.

Re: Export to excel: how do I get values as numbers instead of text?

fukicubiq — Tue, 02 Jan 2018 22:23:45 GMT

Hi Khan,

Thanks for your help, your solution does work but it removes the possibility to use different number formatting on different rows. As I stated in the original post, e.g. in an income statement I would need the same measure to sometimes be formatted as € and sometimes as %, and in the example app, I was mixing € and $ currencies on different rows of same measure (this is a contrived example, not something I would recommend using normally).

Best regards,

Phuoc

Re: Export to excel: how do I get values as numbers instead of text?

sabreyals — Thu, 18 Apr 2019 17:35:37 GMT

Was there any workaround suggested for this in QlikSense? Business users do not like seeing the little green symbol on excel that tells them the col or multiple cols need to be converted from number to text.

Re: Export to excel: how do I get values as numbers instead of text?

emrekaya — Fri, 19 Apr 2019 05:56:50 GMT

I am not sure but can you create another dimension in the string, like
num#(your_dimension) as dimension ?

Re: Export to excel: how do I get values as numbers instead of text?

LeeMadgas — Wed, 28 Jun 2023 14:58:20 GMT

Yes, put money and value in variables