Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FionnM
Contributor III
Contributor III

Incorrect subtotals in pivot table

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 then tried using AGGR():

aggr(sum([Invoice Amount]-[Invoice Payment Amount]), [Invoice Amount], [Invoice Payment Amount], [Voucher Line Item Number])

This also shows the correct balance for each invoice, but only shows subtotals for a handful of suppliers (and I can't see any obvious pattern in which suppliers it shows subtotals for).

I then tried putting the entire expression inside a SUM();

sum(aggr(sum([Invoice Amount]-[Invoice Payment Amount]), [Invoice Amount], [Invoice Payment Amount], [Voucher Line Item Number]))

This had exactly the same result as what I started with.

All I want is for the table to show the correct balance for each invoice, the correct subtotals for each supplier, and the correct balance for all outstanding invoices.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

Probably isn't the right dimension within the aggr() and should be rather the next higher level above the  [Voucher Line Item Number] and might be [Invoice No]. 

sum(aggr((sum([Invoice Amount]-[Invoice Payment Amount])) /
                       count([Voucher Line Item Number]), 
           [Invoice No]))

- Marcus

View solution in original post

4 Replies
marcus_sommer

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.

- Marcus

FionnM
Contributor III
Contributor III
Author

Hi Marcus,

Thanks for your suggestion. I tried it and the table now only shows a small subset of outstanding invoices, and the balances are incorrect.

marcus_sommer

Probably isn't the right dimension within the aggr() and should be rather the next higher level above the  [Voucher Line Item Number] and might be [Invoice No]. 

sum(aggr((sum([Invoice Amount]-[Invoice Payment Amount])) /
                       count([Voucher Line Item Number]), 
           [Invoice No]))

- Marcus

FionnM
Contributor III
Contributor III
Author

Hi Marcus,

That didn't work, but when I replaced [Invoice No] with [Invoice Voucher] it did exactly what I wanted it to do. Thanks so much for your help! Have a great weekend.