Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i want to display the result as shown in image
output: Pivot table
dimension :FiscalPeriod
Measure:
sum(SW)
sum(VRC)
sum(SSTC)
Hi Sony,
did you try to apply the solution reported here Re: Number formatting - millions to billions or this one Format Number (in Thousand) ?
Let us know.
Simone
thnks it is working it seems but if i want "K" how can i include in this expression?
in below expression only B and M is there i want to include K also how to do this?
=If(sum(Amount)/1000000000 > 1,Num(sum(Amount)/1000000000,'$#,###B'), Num(sum(Amount)/1000000,'$#,###M'))
and in this i dont want comma how i can remove comma and include $ symbol
how can i do this?
Hi,
please try this:
=If(sum(SW)/1000000000 > 1,Num(round(sum(SW)/1000000000),'$ ####B'), If(sum(SW)/1000000>1,Num(round(sum(SW)/1000000),'$ ####M'), Num(round(sum(SW)/1000),'$ ####K')))
Let me know 🙂
Simone
thanks sir its working in my sample app.
i need to test in real data and then i will close this one.
Hi,
one example using your sample data:
SET vSize = Div(Log10($1),3);
SET vFormatNumber = Dual(Num($1/Pow(10,3*$(vSize($1))),'$#,##0')&' '&Pick($(vSize($1)),'K','M','B','T'),$1);
table1:
LOAD [Fiscal Period],
Ceil(Rand()*100) as SomeDimension,
SW,
VRC,
[Grand Total],
SSTC,
HHTC
FROM [https://community.qlik.com/servlet/JiveServlet/download/1538862-336899/Dim_Test.xlsx] (ooxml, embedded labels, table is Sheet2);
hope this helps
regards
Marco