topic Re: Number formatting in dimensions in QlikView

Number formatting in dimensions

Thu, 12 Mar 2015 14:47:43 GMT

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?

Re: Number formatting in dimensions

Thu, 12 Mar 2015 14:56:37 GMT

The issue is not with the dimension, i believe. Just set your formating for the expression right from the Number tab.

Re: Number formatting in dimensions

Gysbert_Wassenaar — Thu, 12 Mar 2015 14:57:24 GMT

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.

Then your number values are not numbers. Num() can format only numbers, not text. Use the Num#() function to create numbers from text values first.

Re: Number formatting in dimensions

Thu, 12 Mar 2015 14:58:01 GMT

I have already set the number formatting in the document properties...

Re: Number formatting in dimensions

Thu, 12 Mar 2015 15:01:50 GMT

I've just tried that, and it does fix the numbering issue.

What i've done:

- Change the document properties for number format

- Used num(num#(Dimension))

However; the export to excel still fails to see the column as a number.

Re: Number formatting in dimensions

simenkg — Thu, 12 Mar 2015 15:02:15 GMT

You set the system variabel "ExcelExportMixedAsText" to 0.

To do this in the Client go to: Help - About QlikView - right click the "Q" in the lower left corner - Scroll to ExcelExportMixedAsText and set the value to 0.

To do this on the server go to:

C:\ProgramData\QlikTech\QlikViewServer or C:\Documents and Settings\All users\ApplicationData\QlikTech\QlikViewServer depending on OS)

Edit the Settings.ini file and add a line

ExcelExportMixedAsText = 0

Do this at your own risk, as changing these settings are not recommended by Qlik.

Re: Number formatting in dimensions

Thu, 12 Mar 2015 15:03:37 GMT

Ran into that during my extensive search.

That setting is already set to 0, however, no resolution i'm afraid

Even multiplying the dimension by 1 or another number does not help.

Re: Number formatting in dimensions

simenkg — Thu, 12 Mar 2015 15:05:50 GMT

Try using rangesum around the expression. It defaults to 0.

Re: Number formatting in dimensions

Thu, 12 Mar 2015 15:08:04 GMT

Tried Rangesum(num#(Dimension)), no luck.

Re: Number formatting in dimensions

Gysbert_Wassenaar — Thu, 12 Mar 2015 15:10:20 GMT

Can you post a small qlikview document that demonstrates the problem?

Re: Number formatting in dimensions

Thu, 12 Mar 2015 15:12:07 GMT

Thought of that, but that's a bit difficult. Total environment is around 65 megabytes with personal information

You guys aren't running into the same issues (i.e.; it should be fixable?)?

Re: Number formatting in dimensions

Gysbert_Wassenaar — Thu, 12 Mar 2015 15:13:02 GMT

That's what reduction and scrambling are for: Preparing examples for Upload - Reduction and Data Scrambling

Re: Number formatting in dimensions

Thu, 12 Mar 2015 15:29:53 GMT

I've masked as much as possible. Hope this helps.

It concernc (for instance!) the third column in the test1 chart and the first column in the small chart.

Re: Number formatting in dimensions

Gysbert_Wassenaar — Thu, 12 Mar 2015 17:07:09 GMT

The document you uploaded contains section access, so I can't open it.

Re: Number formatting in dimensions

Thu, 12 Mar 2015 19:37:31 GMT

My apologies, username is QADMIN, password is XS4RNA.

Re: Number formatting in dimensions

prieper — Thu, 12 Mar 2015 20:29:51 GMT

Add "0" to your expression, thus to force to always show a figure. This may assist Excel to choose the correct datatype.

Edith says: you better check your datamodel, as this number is not distinct, thus not being able to show a result.

HTH Peter

Re: Number formatting in dimensions

Thu, 12 Mar 2015 20:57:24 GMT

could you please Try Num(NUM#(),####), #### stands for the format you are looking for.

Thanks,

Re: Number formatting in dimensions

PrashantSangle — Fri, 13 Mar 2015 05:20:04 GMT

Hi,

Which Qlikview Version are you using??

Since it is bug some previous version.

Regards

Re: Number formatting in dimensions

Fri, 13 Mar 2015 07:18:42 GMT

I've already tried that, to no avail.

Re: Number formatting in dimensions

Fri, 13 Mar 2015 07:19:38 GMT

Tried that too, by forcing a 1.

What would the data model alter that the number formatting in document properties wouldnt?