topic Re: Money formatting Millions and thousands in load script in App Development

Money formatting Millions and thousands in load script

M_Jay — Fri, 29 Jan 2021 22:25:16 GMT

Hi,

My amount field has millions, thousands and hundreds as values. How do I convert into Money($) format as "xxxM", "xxxK", and "xxx" based on values in load script?

Thanks

Malini

Re: Money formatting Millions and thousands in load script

rubenmarin — Sat, 30 Jan 2021 08:49:32 GMT

Hi @M_Jay, I don't thin you have to do this on script, but on front-end, in script you can have an amount in xxxK and another in xxxM, if you sum both the result will be in xxxM.

You can decide to divide all amounts by 1000 to have all values in k, or in M, but you need to have all amounts in the same grade as source: units, k's, M's...

On front-end using automatic format qlik sense may apply the correspondent multiplier to show as units, k, or M depending of the value, or you can control with a predefined expression like:

If(fabs([YourExpression])>1e6, Num(([YourExpression]/1e6),'#.##0,00M'),
If(fabs([YourExpression])>1e3, Num(([YourExpression]/1e3),'#.##0,00k'),
Num(([YourExpression]),'#.##0,00')
))

Re: Money formatting Millions and thousands in load script

valpassos — Fri, 05 Mar 2021 13:28:34 GMT

Hi @rubenmarin ,

Maybe you can help me with this?:

I'm trying to encapsulate the division by 1000000 in a formatting function that I can then"call" in a KPI object.

For instance: in the front-end, I want to show the number 21,784,123€ as 21.8 M€. I have the mask in a SET variable to show the M€ sign, but how can I perform the division in the script and use it in the chart object as Num(SUM(#Sales),vFormatEURMillion) ?

Thanks in advance,

Lisa

Re: Money formatting Millions and thousands in load script

rubenmarin — Sat, 06 Mar 2021 10:45:10 GMT

Hi @valpassos, I'm not sure to understand what youa re trying or where you find the issue. Sometines I use a selector to chek if you want values in units, miles or millions, I do taht with a varible vDivisor that can have 1, 1000 or 1000000 as value, and all expressions are divide by this vDivisor variable, maybe you can use something similar.

Re: Money formatting Millions and thousands in load script

valpassos — Mon, 08 Mar 2021 10:54:15 GMT

Ohh that's a good ideia! Haven't though of that. But imagine that I want to trigger that division in the script, with no need for a selector units/millions/... in the front-end. Is that possible?

Thanks!

Re: Money formatting Millions and thousands in load script

rubenmarin — Wed, 10 Mar 2021 18:53:11 GMT

If you want to do that in script the basis are the same, create a variable with the divisor value an apply that divisor when loading values, doing this on script will requirte a reload to apply changes.

LOAD Amount/$(vDivisor) as Amount...