Number Formatting issue while changing billions/Millions in trend line chart

[kdr_Qv]

Hi Guys..

1.Do we have  any options to set 1 decimal point  while using number format  as 'AUTO' ,by default qliksense displaying 2 decimal points in auto format.

to achieve this  i have done below logic 

1. set  as single decimal point   2. to convert my numbers to  Millions(#M),Billions(#B),Thousands(#k).. 

But When i use trend line chart  with same logic.. chart not displaying on unit type.  trendline showing only based on value.. (due to hard coded..) 

=if( $(Value1)<1000000, Num($(Value1)/1000,'#.0'&'K)'), if( $(Value1)<1000000000 , Num($(Value1)/1000000,'#.0MM'),
Num($(Value1)/1000000000,'#.0B')))

Choosing Number format as "measure Expression"

Trend Chart  below showing  4.5MM to 640.0 k  going up arrow instead of downfall..  is there any way to  display proper line..   

Could you please help me out alternate way...   

@Sunny@qlik

[RPlagge]

Thank you JonasValleskog for that solution!

It would be nice if Qlik could create a format mask for grouping like Num(Sum(Value), '#,###.# G') where G stands for "Grouping" or something like that. (Same with Money(Sum(Value), '#,###.# G') )

[taha_mansoor]

The I tried syntax but it fails on negative numbers. It just shows null/empty. Any solution please ?

[taha_mansoor]

Did you get any solution for negative value ?

[RPlagge]

Building from @JonasValleskog 's answer: https://community.qlik.com/t5/New-to-Qlik-Sense/Number-Formatting-issue-while-changing-billions-Mill... 

To describe the issue with negative values, the logarithm (log) function does not work with negative numbers because you cannot raise 10 to a power to get a result using negative value (y) in  log10(y) = x  because  10^x  must equal  y. By rule, the logarithm base (b) must be greater than 1.

For reference, mathematically, logarithm function is read as "logB(y) = x" or "Log base B of y equals x". Normally the base is with lowercase "b" but I am using uppercase for the sake of clarity. Also, for logarithm to work, the converse must be true. As such, the function  B^x = y  must be true, where x is the result of  logB(y).

To solve your negative value issue, you could try wrapping the value with the absolute value  FABS(x)  function.

dual(num(sum(Value)/pow(10,3*floor(log10(FABS(sum(Value)))/3)),'#,###.#'&pick(floor(log10(FABS(sum(Value)))/3),'K','M','B')),sum(Value))

[taha_mansoor]

@RPlagge Thanks for the answer. However, we must show the -ve sign along with the figure so there is no chance to go for absolute value. I guess this is the limitation of this approach that we should keep in our minds before applying it.

Thanks 

[RPlagge]

@taha_mansoor No worries. The formula is rather hairy, so I can see the confusion.

The first and last "Sum(values)" were left alone, so that will still show the negative values.

The breakdown of the formula is:
(In this case, will substitute "sum(values)" with "X").

Original formula, with substitution:
dual(num(X/pow(10,3*floor(log10(FABS(X))/3)),'#,###.#'&pick(floor(log10(FABS(X))/3),'K','M','B')),X)

I don't know exactly what dual does, but it seems to serve a purpose in calculation with  dual(text, number). The number in this case is the last  "X" in the expression.

For the text portion of dual(text, number), that is where this comes in play. It is trying to get the value "X". in grouped numerical fashion like "$ 5.3 K" for example, but because that is represented with some non-numerical characters, it is therefore a text.

num(value, format) casts the numerical format into a number. So to determine its value, we need to make them into the lowest thousand group (0-999). For instance, 15 million is written as 15,000,000, so the smallest version is 15 because 15 is between 0 and 999. To do that, they divide the original value X by 10 to the power of 3 times the whole number of 1og10(X) divided by 3. This would give a clean division. In the case of 15 million, log10((15000000))/3 is 2.39203042, so the whole number version of that is 2. 2 * 3 is 6. Therefore, 10^6 is 1 million. From that, we can convert 15,000,000 into 15. In this case, even if you had X as a negative value, it would work if you left the first X alone and wrapped the remaining Xs with FABS().

The format value is '$ #,###' which tells Qlik how to format the number. (It's a standard money formatting mask without the decimals, and assuming the grouping separator is the comma). Although, we want to add a numerical group notation (K, M, B, etc.), so to do that, that's where the next seemingly complicated formula comes into play. The formula for this is   pick(floor(log10(X)/3), 'K', 'M', 'B').  It's taking the logarithmic value of X and dividing it by 3 to determine which item to use in the pick formula. log10(X)/3 is necessary to determine which group it belongs to, so if X is between 1,000-999,999, then log10(X) will be between 3 and 5.9. That divided by 3 will be between 1.0 and 1.9, so the floor version of that will be 1, which results in 'K' from the pick. If X was between 1,000,000 and 999,999,999, then the logarithmic would be between 6 and 8.9. (The result is too large, so most calculators will actually round this to 9, but the result is not really 9). So that divided by 3 will be between 2 and 2.9. The floor of that is 2; ergo, the result is 'M'. In this case, we want to use absolute value version of the value using FABS(X), which will still work as intended.

If I understand exactly what you're looking for, the only thing that needs negative value is the number itself as in "$ -50K", right? In that case, the only two things that do not need the absolute functions are the first X in the value part of  num(value, format), and the number part of the  dual(text, number).

Does this help?

[JonasValleskog]

@taha_mansoor do try out the solution that @RPlagge proposed above. When giving it a go independently, I came up with exactly the same formula and can confirm it works. The fabs() function is just there to work out the order of magnitude when deriving the correct number format pattern, but the calculated number that the pattern applies to still retains it's sign. I can't re-upload an updated solution as I'm working in a secure environment right now on a client laptop, but just copy paste the solution @RPlagge  supplied, or my own implementation of the same below for convenience:

dual(num(sum(Value)/pow(10,3*floor(log10(fabs(sum(Value)))/3)),'#,###.#'&pick(floor(log10(fabs(sum(Value)))/3),'K','M','B')),sum(Value)) 

just remember to substitute: sum(Value) with your own measure formula.