topic show Million and Billion in QlikView

show Million and Billion

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

Hi All,

i have a data like below

Tab:

Load * Inline [

Name,Produt,sales

raju,A,10000000

siva,B,200000000

yannie,C,30000000

anu,D,60700000

venkat,E,50000000]

;

exit SCRIPT;

My requirement is:

i have a stright table with

dimension: product

Measure: sum(Sales)

so it will give me result as sum of sales. But my user need a listbox with million and billion so when user selects the table result should show based on selection

how can i do this?

Thanks

Sony

Re: show Million and Billion

sibin_jacob — Wed, 08 Aug 2018 07:38:09 GMT

If(Selected_Column='million',

Num(sum(Sales)/1000000,'$#,###M')

If(Selected_Column='billion',

Num(sum(Sales)/1000000000,'$#,###B')

))

Re: show Million and Billion

soniasweety — Wed, 08 Aug 2018 07:39:50 GMT

thanks . but user needs a listbox with Billion and Million

is that expression will be in table right? so how user can select?

Re: show Million and Billion

sibin_jacob — Wed, 08 Aug 2018 07:40:58 GMT

I Have edited the above expression.

Listbox column is Selected_Column

If(Selected_Column='million',

Num(sum(Sales)/1000000,'$#,###M')

If(Selected_Column='billion',

Num(sum(Sales)/1000000000,'$#,###B')

))

Re: show Million and Billion

pooja_prabhu_n — Wed, 08 Aug 2018 07:58:03 GMT

Hi,

Just addition to the expression sibin.jacob

You can specify the default format when nothing is selected.

T1:

Load * Inline [

Name,Produt,sales

raju,A,10000000

siva,B,200000000

yannie,C,30000000

anu,D,60700000

venkat,E,50000000]

;

T2:

LOAD * Inline

[

Currency

Million

Billion

];

If(Currency='Million',Num(sum(sales)/1000000,'$#,###M'),

If(Currency='Billion',Num(sum(sales)/1000000000,'$#,###B'),Sum(sales)

))

Re: show Million and Billion

sasiparupudi1 — Wed, 08 Aug 2018 08:28:41 GMT

Hi Try using a variable

vFormatNumber

with value

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

You may then call the variable like

$(vFormatNumber(10000000023))

hth

Sasi