Format dimension in table as a number/percentage without altering the underlying datamodel

[cpomeren003]

Hi everyone,

I am currently working with an Excel file that has columns that both contain percentages and text values. In Excel it looks  something like this:


As you can see the percentage value in the cell is "0%" and it's formatted as a percentage that results in it displaying like: "0,00%". The text is formatted as text and just shows the value.

If I import this in Qlik Sense it looks like this:

And if I export this I get:

As you can see we lost the percentage formatting in both Excel and Qlik Sense. Makes sense though, since Qlik only gets the values from Excel.

So to solve this I changed my script to:

"if(isNum([Text or Percentage]) = -1, num([Text or Percentage], '#,##%'), Text([Text or Percentage])) as [Text or Percentage]"

So the logic behind this is, if it's a number -> format it as a number/percentage and if it's text -> format it as text.

This results in:

And if I export this I get:


As you can see, the value has been changed to "0,00%" and is saved as text, hence the error: "Number Stored as Text".

So I think I understand what is happening, my Qlik Script is transforming the "0%" to "0,00%" and will also export "0,00%". Excel recognizes this as text because it expects "0%".

So my next step was to change the presentation of the value instead of the value itself, but unfortunately I only get the option: "Text" and "URL", see:


But I want to represent it as a "Number", which is possible if I add it as a measure instead of a dimension. So I removed my script that edits the values and instead added my values as a measure and changed the formatting:

 

And if I try to export that I get this:


This is great, both my visuals in Qlik Sense as my values in Excel are correct. The only problem is, I need my "Text or Percentage" to be a dimension and not a measure.

So my question is, is there any way I can achieve this at the same time:

1. Field needs to be a dimension of a Qlik Sense table;
2. Values of field need to display as #,##% in the Qlik Sense table;
3. If I export the table to Excel it should automatically be recognized as a percentage in Excel.

If you have any idea on how I can achieve this, I would really appreciate the help.

[Jerry_Jin]

Hi - In your data load script, when you format the field as: num(FieldName, '#,##0.00%') as FieldName, would that come out the way you wanted?

[cpomeren003]

Hi,

If I try that I get:

And these values are normally ##,##%.

If I export this, Excel also doesn't see them as percentages.

Thanks for the help though, because I still haven't found a solution that works in Qlik Sense and Excel.

[Jerry_Jin]

So I think the num() just displays the value as a number. Maybe in this case you wanna try to convert the value to a number using num#() so you can use it as a dimension? So how about this:

num(num#(FieldName) '#,##%')

Also found a couple articles that might help:

https://community.qlik.com/t5/QlikView-App-Development/What-is-the-difference-between-Num-and-Num-fu...

Interpretation Function

Conventions for numbers and formats