Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lanlizgu
Creator III
Creator III

Customize numbers format and legend issue

Based on the below image, I am having two number format issues when developing in Qlik Sense:

  1. Qlik Sense is not able to customize numbers format as done in Qlikview: I would like to have in the graph the numbers with M in currency and not having the annoying G (in case of displaying less than a M, K would show and later full number)
  2. There is a discrepancy between the number format of the legend and the graph. Graph numbers can be modified as money number inside measures and number format. However, when  I customize it inside the expresssion of colors and legend, it won't give format to the number (num($(Variable),'##.###0,00 €','-##.###0,00 €'))

asdf.JPG

Thank you.

1 Solution

Accepted Solutions
lanlizgu
Creator III
Creator III

At the end I was able to close the two points.

With

SET NumericalAbbreviation='6:M;-6:M';

I was able to avoid the G and K and display just millions.

The orevious also fixed the discrepancy between the legend and the graph.

The thing is that when I publish the app in the server (replacing the existing one) the data was not correctly displayed and was fixed uploading it as a new one. Don't know if this is a real issue of the QMC.

View solution in original post

12 Replies
lanlizgu
Creator III
Creator III

At the end I was able to close the two points.

With

SET NumericalAbbreviation='6:M;-6:M';

I was able to avoid the G and K and display just millions.

The orevious also fixed the discrepancy between the legend and the graph.

The thing is that when I publish the app in the server (replacing the existing one) the data was not correctly displayed and was fixed uploading it as a new one. Don't know if this is a real issue of the QMC.

View solution in original post

Vince_CH
Creator II
Creator II

@lanlizgu: Hello, I have a similar issue as yours, and tried your ways, but didn't work out, can you help to take a look at this: as picture illustrated, in bar chart the highest bar hits with number ¥2,706K, while the right side legend number format is in 2.71k (should be 2.71M here), which does't show the number in format correctly. The existence of K and M can be allowed in my case. The expressions in the measures are like following. I have tried to remove the num format from the expressions and put it under number format of measures, unfortunately, the legend number format kept as 2.71k without changes. how shall make the paragraph number and legend number in consistency?? thanks! num(Sum({<[Order Type]={'Y400','Y410'},Order_Responsible_Person-={'GENGFEI'},Year,Quarter,Month,Week,Date={"$(='>='&Date(AddYears(YearStart(Max(Date),0,7),0), 'DD.MM.YYYY') & '<='&Date(AddYears(Max(Date),0), 'DD.MM.YYYY'))"}>}Order_Total_Value_Net_Local_Curreny) /1000,'¥#,##0K;($#,##0K)')
lanlizgu
Creator III
Creator III

Hi Huichunchang,

Could you please share an example without real data and without the script (just the sets) so I could take a look?

What do you have in the script editor for the SET NumericalAbbreviation?

Thank you.

 

Best regards.

Vince_CH
Creator II
Creator II

Hello, thanks for reply.
unfortunately I am not able to attach qvf file. but the mentioned point is load editor is like following:
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
lanlizgu
Creator III
Creator III

Based on your expression:

  • Why are you formatting the expression with the num function? Why don't you format them directly in QlikSense?
  • when do you need to put ¥ and when $?
  • which is your:
    • SET ThousandSep
      SET DecimalSep
      SET MoneyThousandSep
      SET MoneyDecimalSep
      SET MoneyFormat

In case you just want to show ¥, what happens if you try replacing the NumericalAbbreviation by the following?

SET NumericalAbbreviation='3:k ¥;6:M ¥;9:G ¥;-3:k ¥;-6:M ¥;-9:G ¥';

Once we have the previous info I understand that we could do what you are trying to achieve.

 

Thank you.

 

 

 

Vince_CH
Creator II
Creator II

@lanlizgu:
The reason to format in expression by num is to set up the right format as desired with currency mark as well as k or M. Currency mark is needed on case of sales or cost value.
The setting in my editor is as the bottom indicated.

Yes, I may try below as you suggested, but this might apply to all expressions where no currency mark is needed, then it leads to another issue?
SET NumericalAbbreviation='3:k ¥;6:M ¥;9:G ¥;-3:k ¥;-6:M ¥;-9:G ¥';
---------------------------------------------------------------------------------------------------
Setting in the editor:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='¥#,##0.00;-¥#,##0.00';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY TTh:mm:ss[.fff]';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='zh-CN';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
lanlizgu
Creator III
Creator III

@Vince_CH So you have two possibilities here:

  • Put in the expression correctly the num function based on your requirements
  • For each measure format the numerical with the editor of QlikSense based on your requirements and having the SET NumericalAbbreviation='3:¥k;6:¥M;9:¥G;-3:¥k;-6:¥M;-9:¥G';
  • asd.JPG

 

Vince_CH
Creator II
Creator II

@lanlizgu:
For option 1, my previous expression is as following, it works well with the chart label, say, ¥2,706K as the result, but in the mini chart besides with label in ¥2.71K unfortunately.
num(Sum({<......>}Order_Total_Value_Net_Local_Curreny)/1000,'¥#,##0K;($#,##0K)')

For option 2, as mentioned, if I format the following in the script, it may apply to all numbers where i don't need the currency mark as well.
SET NumericalAbbreviation='3:¥k;6:¥M;9:¥G;-3:¥k;-6:¥M;-9:¥G';
lanlizgu
Creator III
Creator III

why are you dividing the expression by /1000?

If in Aspects-->Colour and Legend -->Select measure and you delete the /1000 it should work