topic Issue with multiple number formats in a pivot table in QlikView

Issue with multiple number formats in a pivot table

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

I have an unusual issue with number formatting in a pivot table I have created. Basically I need to be able to show some measures as percentages and some as whole numbers. To do this I have specified the number formats in the expression as follows:

=if(match([Measure ID],11,226,227,228) num(sum(Measures.Numerator),'#,##0'),

num(Measures.Numerator/Measures.Denominator, '#.#0%'))

Where this doesn't work is if the whole number is a 1 then this displays as 100.00% and conversely if the percentage is 0 then I want it to show as 0.00% but it's showing as 0

Has anyone got a workaround for this?

Thanks

Re: Issue with multiple number formats in a pivot table

sunny_talwar — Tue, 13 Mar 2018 17:23:48 GMT

Would you be able to share you file to take a look at this?

Re: Issue with multiple number formats in a pivot table

Nagaraju_KCS — Tue, 13 Mar 2018 17:46:28 GMT

Goto Number tab ==> Set Number Format Settings as below

Re: Issue with multiple number formats in a pivot table

Nagaraju_KCS — Tue, 13 Mar 2018 17:58:08 GMT

Use this format...

This is working for me....Zero's as well showing 0.00%.

= #,##0.00%

IF(ID=100, Num(Sum(Amount)/Sum(TOTAL Amount),'#,##0.00%'), Num(Sum(Amount),'#,##0' ))

Re: Issue with multiple number formats in a pivot table

rosey13478 — Wed, 14 Mar 2018 11:54:35 GMT

None of the straight forward number formatting changes worked, so have found a workaround whereby in the load if the value is zero I change this to 0.00000001 so it forces the percentage calculation but has no effect on how the 0.00% is shown in the table.

Thanks all for your help though