I have invoice data in one table that is based on order and invoice number. In the pivot table, they are also linked with the object to which the invoice applies; this works fine as long as invoice:object is 1:1. However, in some instances, one invoice covers multiple objects, so there the whole invoice-sum is displayed in every line, but just counts once towards total.
The users now want to see on each line only the invoice-value divided by the number of objects. Is there any way to do this? I tried it by dividing the invoice value by aggr(count (distinct object_id), invoice_id) - but this resulted in the value just being displayed in a single line once for the entire invoice.
So, is there any way to achieve the desired result?
Based on what you described - and it's not a data structure I'd recommend using, but you might be stuck with it - you could try something along the lines of:
* Take the only invoice sum (since it's actually a header field being displayed at line levels and we don't want to repeat it)
* Divide by the total count of invoice lines for this invoice (this assumes an object_id can't repeat within an invoice and also that the sum should be divided evenly in every situation)
* Multiply by the count of object id. For each line, this will be 1 so it won't change the result, but for the subtotal lines it'll be identical to the number of lines we counted in the previous part so they'll offset and the number will just display as-is.
Another option is to use Dimensionality() to recognize if you're in a subtotal / total or line level, e.g. sum(invoice_sum) / if(Dimensionality() >= 1,count(total <invoice_id> object_id,1)
Based on what you described - and it's not a data structure I'd recommend using, but you might be stuck with it - you could try something along the lines of:
* Take the only invoice sum (since it's actually a header field being displayed at line levels and we don't want to repeat it)
* Divide by the total count of invoice lines for this invoice (this assumes an object_id can't repeat within an invoice and also that the sum should be divided evenly in every situation)
* Multiply by the count of object id. For each line, this will be 1 so it won't change the result, but for the subtotal lines it'll be identical to the number of lines we counted in the previous part so they'll offset and the number will just display as-is.
Another option is to use Dimensionality() to recognize if you're in a subtotal / total or line level, e.g. sum(invoice_sum) / if(Dimensionality() >= 1,count(total <invoice_id> object_id,1)
Thank you so much! A combination of dimensionality and aggr() worked for me - I had completely forgotten that dimensionality existed. We should all put together best-practices refresher courses, so that we remember all the nifty stuff the code can do. 😉
Answering questions (or reading answers) on QlikCommunity keeps you sharp insofar as what can be done and how! There's a lot of things I don't remember how to do, but at long as I vaguely remember that they *can* be done, I'll know to search them out next time I need them.
