Count items for invoice

[dimak123]

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

[#2]

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

[tresesco]

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)

....

[sasikanth]

hi,

Did you check my attachement ?

[PradeepReddy]

see the attachment.., it might be helpful..