Error in Expression when trying to handle M or K values together

[Akshaya]

Hi All,

I am working on an expression in qliksense.

I have created a variable as below in the script:

SET vScaleNumber=if($1>1E6, num($1/1E6,'#,##0.00M')
,if($1>1E3, num($1/1E3,'#,##0.00K')
,num($1,'#,##0.00')
));

And based on this variable, i am trying to handle the large numbers in M or K format.

So if I am writing a simple expression, its working fine as per below:

But if I am trying to include more parameters in the expression, its throwing an error, as per below:

I guess comma is causing the issue here, but how to fix it, I am not sure. I tried using 'and' also instead of ',' but no luck.
Could someone please help me out here with the expression.

Thank you so much in Advance!

Akshaya

@MayilVahanan @GeorgePhilips23 
@rwunderlich 

[Bill_Britt]

Hi,

Take a look at this post.

https://community.qlik.com/t5/QlikView-App-Dev/Number-formatting-millions-to-billions/m-p/603696

Bill

[Akshaya]

Hi Bill, 
I checked the link you mentioned, and tried something like that, but no luck. Still stuck.

Thanks,

Akshaya

[marcus_sommer]

What you are trying to do isn't possible - at least not as a general solution within a parametrized variable. It will work with a field-reference and with expressions which doesn't contain any comma. The reason is that each included comma is treated as parameter-delimiter of the variable. Unfortunately, there is no way to mask a comma.

Workarounds may be to replace the comma within the call with another char and replacing it back within the variable or using different functions respectively the syntax within the call to avoid any commas. In regard to your example you may change:

sum({< F1 = {'x'}, F2 = {'y'}>} F3)

to

sum({< F1 = {'x'} > * < F2 = {'y'}>} F3)

- Marcus

[Akshaya]

Hi @marcus_sommer ,

Thanks for you quick response!
I tried what you have mentioned above, and it worked but one thing is its giving me the round off values.

As in if I am supposed to get 17.94M so its giving me 18.
Could you please let me know how can I fix that and avoid that rounding off?

Thanks,

Akshaya

[marcus_sommer]

Make sure that your object applies the formatting from the expression and not any default format-setting from the object-properties.

- Marcus