Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day,
need some help with aggregations:
I want to show a list of clients and to see how many invoices(just count) contained only one item, 2 items, 3 items and 4.
this is my data:
CustomerId | InvoiceId | InvoiceLine | ItemId | Quantity |
100237 | 01390082_INV | 3 | 10692016 | 2 |
100237 | 01390082_INV | 4 | 10692016 | 1 |
100237 | 01390082_INV | 5 | 10692016 | 2 |
100237 | 01390082_INV | 6 | 10692016 | 2 |
100237 | 01390082_INV | 7 | 10692016 | 2 |
100237 | 01390082_INV | 8 | 10692016 | 2 |
100083 | 01390090_INV | 1 | 10281004 | 4 |
100083 | 01390090_INV | 2 | 10692016 | 1 |
100083 | 01390090_INV | 3 | 10281004 | 2 |
100083 | 01390090_INV | 4 | 10281004 | 2 |
100083 | 01390090_INV | 5 | 10281004 | 1 |
100083 | 01390090_INV | 6 | 10281004 | 4 |
100083 | 01390090_INV | 7 | 10281004 | 4 |
many thanks a head
Hi Dima,
As per the given input are you expecting the output like below?
CustomerId | 1 item for Invoice | 2 items for Invoice | 3 items for Invoice | 4 items for Invoice |
100237 | 1 | 5 | 0 | 0 |
100083 | 1 | 2 | 0 | 3 |
Thanks,
Sreeman
Try expressions like:
For 1 Item Invoices:
Count({<InvoiceId={"=Count(Distinct ItemId)=1"}>} Distinct InvoiceId)
For 2 Item Invoices:
Count({<InvoiceId={"=Count(Distinct ItemId)=2"}>} Distinct InvoiceId)
....
hi,
Did you check my attachement ?
see the attachment.., it might be helpful..