topic Re: Formatting numbers with VBscript function in QlikView

Formatting numbers with VBscript function

RedSky001 — Mon, 07 Nov 2011 14:34:06 GMT

I'd like to write a VBscript function to format numbers and use it within chart expressions. Could someone provide the correct syntax
(Appreciate the below code includes num() which is not vb script)

function formatNumber(parameter)

if(parameter > 1000000
or $parameter < -1000000
,num((parameter / 1000000),'#,##0 Mil')
,if(parameter > 1000
or parameter < -1000
,num((parameter / 1000),'#,##0 K')
,num($1,'#,##0')
))

end Function

Regards

Mark

Message was edited by: msheraton After reading QlikView Automation Reference it appears user defined functions cannot be called in Expressions. So I'll have to do this another way...

Re: Formatting numbers with VBscript function

Sat, 28 Jun 2014 18:02:23 GMT

What is a workaround to this, if i want to only use VBScript ?

Re: Formatting numbers with VBscript function

MarcoWedel — Sun, 29 Jun 2014 08:29:29 GMT

Hi Mark,

you could try using a parameterized variable.

regards

Marco

Re: Re: Formatting numbers with VBscript function

MarcoWedel — Sun, 29 Jun 2014 22:06:15 GMT

might be a bit late

but maybe someone is searching for something like this:

SET vSize = Div(Log10($1),3);
SET vFormatNumber = Dual(Num($1/Pow(10,3*$(vSize($1))),'#,##0')&' '&Pick($(vSize($1)),'K','Mil','Bil','Tril'),$1);

LOAD *, $(vFormatNumber(parameter)) as paramFormat;
LOAD pow(10,Rand()*14) as parameter
AutoGenerate 30;

regards

Marco