Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

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

1 Solution

Accepted Solutions
pooja_prabhu_n
Creator III
Creator III

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)

))

View solution in original post

5 Replies
sibin_jacob
Creator III
Creator III

If(Selected_Column='million',

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

If(Selected_Column='billion',

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

))

soniasweety
Master
Master
Author

thanks .  but user needs a listbox with Billion and Million  

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

sibin_jacob
Creator III
Creator III

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
Creator III
Creator III

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
Master III
Master III

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