Hi folks. I've recently taken over as Qlik administrator in my company and I'm having a bit of trouble with a pivot table my predecessor built.
The pivot table pulls invoices from an external database. The dimensions are "Supplier Code", "Supplier Name", "Invoice Date", "Invoice No", "Invoice Amount" and "Invoice Payment Amount". The expression "Balance Due" finds the remaining balance against each invoice like so:
sum([Invoice Amount]-[Invoice Payment Amount])
A colleague flagged to me that the figures listed under "Balance Due" were wrong, and I discovered that, for whatever reason, the expression is multiplying the actual balance of the invoice by the number of line items on the invoice (e.g. if the invoice was €100, of which €50 had been paid, and there were three line items on the invoice, the expression was showing the balance due as €150).
I edited the expression to divide the balance due by the number of line items on the invoice:
sum([Invoice Amount]-[Invoice Payment Amount])/count([Voucher Line Item Number])
This shows the correct balance for each invoice, but the subtotals for each supplier are now wrong.
I think you have the wrong expression and dimensionalities within the aggr() and it should rather look like:
sum(aggr((sum([Invoice Amount]-[Invoice Payment Amount])) / count([Voucher Line Item Number]), [Voucher Line Item Number]))
Beside this I suggest to review the datamodel because the table-structures and/or the associations between the tables may not suitable for your needs. Ideally a calculation could in the UI be made with sum(Field) and don't need a correction-count because any duplication or even cartesian products must be compensated.
For pragmatically reasons I did use also such logics but mostly I did this kind of correction count already within the script to be able to use more simplified expressions in the UI and to get a better performance because aggr() could make the calculations very slow.