Money formatting Millions and thousands in load script

[M_Jay]

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

[rubenmarin]

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')
))

[valpassos]

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

[rubenmarin]

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.

[valpassos]

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!

[rubenmarin]

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...