Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

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

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:

qlik1.PNG
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:
qlik2.PNG
And if I export this I get:
qlik3.PNG
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:
qlik4.PNG
And if I export this I get:
qlik5.PNG

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:
qlik6.PNG

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:

qlik7.PNG 

And if I try to export that I get this:
qlik8.PNG

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.

3 Replies
Employee
Employee

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

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?
Partner
Partner

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

Hi,

If I try that I get:
qlik21.PNG
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.

Employee
Employee

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

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