topic Re: Error in Expression when trying to handle M or K values together in QlikView

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

Akshaya — Wed, 16 Nov 2022 14:00:15 GMT

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

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

Bill_Britt — Wed, 16 Nov 2022 11:24:50 GMT

Hi,

Take a look at this post.

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

Bill

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

Akshaya — Wed, 16 Nov 2022 13:41:05 GMT

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

Thanks,

Akshaya

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

marcus_sommer — Wed, 16 Nov 2022 14:15:07 GMT

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)

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

- Marcus

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

Akshaya — Thu, 17 Nov 2022 10:26:29 GMT

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

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

marcus_sommer — Thu, 17 Nov 2022 10:35:44 GMT

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

- Marcus