topic Re: Dynamic Number formating in QlikView

Dynamic Number formating

kicchu465 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Dynamic Number formating

trdandamudi — Mon, 25 Jun 2018 17:34:39 GMT

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

Re: Dynamic Number formating

kicchu465 — Mon, 25 Jun 2018 17:38:34 GMT

Hi Thirumala,

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

Thanks

Re: Dynamic Number formating

trdandamudi — Mon, 25 Jun 2018 17:59:47 GMT

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

Re: Dynamic Number formating

trdandamudi — Mon, 25 Jun 2018 20:16:25 GMT

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

Re: Dynamic Number formating

kicchu465 — Tue, 26 Jun 2018 03:47:57 GMT

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.

Re: Dynamic Number formating

jonathandienst — Tue, 26 Jun 2018 06:17:33 GMT

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

Re: Dynamic Number formating

kicchu465 — Tue, 26 Jun 2018 07:28:34 GMT

Thanks very much Dienst.

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

Thanks

Re: Dynamic Number formating

marcus_sommer — Tue, 26 Jun 2018 08:16:05 GMT

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