topic Re: pivot table with dollor and M in QlikView

pivot table with dollor and M

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

Hi All,

Quy Nguyen

Tresesco B

Sreeni Tulabandula

Marco Wedel

i want to display the result as shown in image

output: Pivot table

dimension :FiscalPeriod

Measure:

sum(SW)

sum(VRC)

sum(SSTC)

Re: pivot table with dollor and M

simospa — Wed, 29 Aug 2018 13:28:13 GMT

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

Re: pivot table with dollor and M

soniasweety — Wed, 29 Aug 2018 14:21:25 GMT

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

Re: pivot table with dollor and M

soniasweety — Wed, 29 Aug 2018 14:23:03 GMT

and in this i dont want comma how i can remove comma and include $ symbol

how can i do this?

Re: pivot table with dollor and M

simospa — Wed, 29 Aug 2018 14:36:50 GMT

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

Re: pivot table with dollor and M

soniasweety — Wed, 29 Aug 2018 15:49:09 GMT

thanks sir its working in my sample app.

i need to test in real data and then i will close this one.

Re: pivot table with dollor and M

MarcoWedel — Wed, 29 Aug 2018 21:19:49 GMT

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