Number formatting in dimensions

[#1]

Hi everyone,

I've been struggling with some issues at the moment.

When i try to export a chart to excel, it shows numbers as text. There have been a lot of topics on that already. Very frustrating that this bug seems to not be fixed.

Usually, you could go around the bug by using the num() function in the dimension.

However, i'm running into the issue that when i do that, i get null values in that column. So that means i get empty cells in Excel.

Anyone got an idea how to fix that?

[Gysbert_Wassenaar]

Well, as far as I can tell, if the field values are text values then any calculated dimension using that field will be exported to excel as text. Call it a quirk or call it a bug.

Perhaps you can use an expression instead. num(num#(Personeelsnummer)) seems to work.

Or you could create a numeric Personeelsnummer field in the script:

Noconcatenate
TempContract:
LOAD *, num#(Personeelsnummer,'#######') as PersoneelsnummerNum
RESIDENT Contract;

DROP TABLE Contract;

RENAME TABLE TempContract TO Contract;

[#3]

Currently running 11.20 sr10, but sr4 had the same issue.

[Ken_Thomas]

There was a bug reported for this behavior for QlikView but it was closed as working as designed. Looks like the format of the calculated dimension when exported to excel always shows up in excel as TEXT. You can make it look just like a date, or a number, but in excel after the export it will be format = text. An expression can be used, and the format of this after export to excel can be a non-text format, such as date etc. I recently published a  knowledge article for this: https://qliksupport.force.com/articles/000060940

(The above was tested on QlikView 12.10 and 12.20 )