Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
M_Jay
Contributor II
Contributor II

Money formatting Millions and thousands in load script

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

 

5 Replies
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
Creator II
Creator II

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
Creator II
Creator II

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