Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

soniasweety
Valued Contributor III

show Million and Billion

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

Tags (2)
1 Solution

Accepted Solutions
pooja_prabhu_n
Contributor III

Re: show Million and Billion

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)

))

5 Replies
sibin_jacob
Contributor III

Re: show Million and Billion

If(Selected_Column='million',

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

If(Selected_Column='billion',

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

))

soniasweety
Valued Contributor III

Re: show Million and Billion

thanks .  but user needs a listbox with Billion and Million  

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

Highlighted
sibin_jacob
Contributor III

Re: show Million and Billion

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

))

pooja_prabhu_n
Contributor III

Re: show Million and Billion

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)

))

sasiparupudi1
Honored Contributor III

Re: show Million and Billion

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