Customize numbers format and legend issue

[lanlizgu]

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)
   • As I can see in this post, this continues being a limitation of Qlik Sense and the workaround just works for tables    Steps to Change : Qlik Sense - Billions number format 'G' to 'B'
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 €'))

Thank you.

[lanlizgu · Accepted solution]

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.

[Vince_CH]

@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]

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]

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]

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]

@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]

@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';
• 

[Vince_CH]

@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]

why are you dividing the expression by /1000?

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