Format dimension in table as a number/percentage without altering the underlying datamodel
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:
Field needs to be a dimension of a Qlik Sense table;
Values of field need to display as #,##% in the Qlik Sense table;
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.