show Million and Billion

[soniasweety]

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

[sibin_jacob]

If(Selected_Column='million',

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

If(Selected_Column='billion',

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

))

[soniasweety]

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]

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 · Accepted solution]

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]

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