Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

Dynamic Number formating

Hi all,

I Have a requirement to change the Number either to Billions/Million/Thousands based on the Measure.

I have tried as below:

SET vL.ScaleNumber =if($1>1000000000, num($1/1000000000,vL.Billion_Format)
,if($1>1000000, num($1/1000000,vL.Million_Format)
,if($1>1000, num($1/1000,vL.Thou_Format)
,if($1<0, num($1/1000,vL.Neg_Thou_Format)
,num($1,'#,##0',vL.Format_Integer)
))));

In the Expression I have given as below:

$(vL.ScaleNumber(Sum({<[Product Family]*={'Rev Mobile Voice and Incoming','Rev Mobile Broadband','Rev Fixed Connectivity Access','Rev M2M Services','Rev One Net'},[Customer Flag]={'1'}>}Measure)))

But the above logic is not working.When I have tried using one filter in the set analysis as below then it is working fine. when I am applying multiple filters it is not working.

$(vL.ScaleNumber(Sum({<[Customer Flag]={'1'}>}Measure)))

So how can I acheive this.

I can simple use If statements for converting the Number format dynamically but it would be if I can get this using the above solution.

Thanks

S k

8 Replies
trdandamudi
Master II
Master II

Go to Numbers Tab on your chart properties and look at the right side bottom. You can give the symbols for Millions and Billions.

kicchu465
Creator
Creator
Author

Hi Thirumala,

I have tried this but that doesn't give my desired result.

Thanks

trdandamudi
Master II
Master II

When you say it is not working for multiple filters, Are you getting any error ?

Can you try the below expression and let me know what you are getting ?

$(vL.ScaleNumber(Sum({<[Product Family]={'Rev Mobile Voice and Incoming','Rev Mobile Broadband','Rev Fixed Connectivity Access','Rev M2M Services','Rev One Net'},[Customer Flag]={'1'}>}Measure)))

trdandamudi
Master II
Master II

When you have multiple filters you will have the issue with the comma which is separating the filters. The below link will fix your issue. Give a try and hope this helps:

Comma problem (,) workaround for dollar sign expansion with parameters

kicchu465
Creator
Creator
Author

Hi,

I have gone through the thread mentioned above by you, But I couldn't able to apply the same logic in my expression.

it looks like I'm missing something.

jonathandienst
Partner
Partner

If you convert the expression to an expression variable you may be able to solve the problem:

Define the expression variable (for example in the load script):

Set vExpr = Sum({<[Product Family]*={'Rev Mobile Voice and Incoming','Rev Mobile Broadband','Rev Fixed Connectivity Access','Rev M2M Services','Rev One Net'},[Customer Flag]={'1'}>}Measure));

Then use the expression variable


$(vL.ScaleNumber(vExpr))


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kicchu465
Creator
Creator
Author

Thanks very much Dienst.

Let me check that and I'll get back to if I face any Issue.

Thanks

marcus_sommer
MVP & Luminary
MVP & Luminary

Unfortunately your approach won't work as a global function because each comma within the parameter will be treated as a parameter-delimiter. This meant you will need to apply any logic to avoid the commas which is in many cases not easy to impossible.

If the calculation should be only done on a global level you could use a $-sign expansion like this one:

$(vL.ScaleNumber($(=Sum({<[Product Family]*={'Rev Mobile Voice and Incoming','Rev Mobile Broadband','Rev Fixed Connectivity Access','Rev M2M Services','Rev One Net'},[Customer Flag]={'1'}>}Measure))))

which is in principle the same like pre-calculating the value within a (LET) variable respectively with a beginning equal-sign "=". With SET or without the "=" the expression-string would be transferred into the variable and the comma-problem remained.

Nevertheless you could use it in this case if you changed the syntax within the set analysis to something like:

$(vL.ScaleNumber(Sum({<[Product Family]*={("Rev Mobile Voice and Incoming"|"Rev Mobile Broadband"|"Rev Fixed Connectivity Access"|"Rev M2M Services"|"Rev One Net")} > + < [Customer Flag]={'1'}>}Measure)))

- Marcus