topic Re: Pivot table percentage error in QlikView

Pivot table percentage error

Anonymous — Tue, 24 Nov 2015 07:06:45 GMT

I need to make this expression [Unit Cost] / [Balance] = [Unit Cost %] in pivot table but it give me a wrong records !

[Unit Cost] >>> sum(Debit-Credit)/Sum(TRXQTY)

[Balance] >>> sum(Debit-Credit)

[Unit Cost %] >>> (sum(Debit-Credit)/Sum(TRXQTY)) / (sum(Debit-Credit))

in the following picture i make the expression [Unit Cost]/434.464

that's why it's give me a true record, but when i change the date the record become wrong.

sorry for bad English

Please check the attachment.

Regards,

Re: Pivot table percentage error

jonathandienst — Tue, 24 Nov 2015 07:33:24 GMT

First, watch out for expressions like Sum(Debit - Credit) - this will return zero for all records where either Debit or Credit is null, even if the other value is not. Rather use

Sum(RangeSum(Debit, -Credit))

Sum(Alt(Debit, 0) - Alt(Credit, 0))

Sum(Debit) - Sum(Credit)

Secondly, a pivot table uses the expression value method for the total, not sum of rows. When the expression is a ratio, the expression value will not be equal to sum of rows

"The ratio of the sums (expression value) is not the same as the sum of the ratios (sum of rows)"

You need Aggr() expressions to get sum of rows in a pivot:

Unit cost:

=Sum(Aggr(Sum(RangeSum(Debit, -Credit)) / Sum(TRXQTY), [Cost Sheet Grouping]))

[Unit Cost %] >>>

=Sum(Aggr(Sum(RangeSum(Debit, -Credit)) / Sum(TRXQTY) / Sum(RangeSum(Debit, -Credit)), [Cost Sheet Grouping]))

Re: Pivot table percentage error

Anonymous — Tue, 24 Nov 2015 07:47:28 GMT

Can you help me with any solution to view the percentage for each unit cost per balance like the picture. I mean, can I use other option except pivot table