Count Quantity 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 according to quantity field.

this is my data:

CustomerId	InvoiceId	InvoiceLine	ItemId	Quantity
100237	01390082_INV	1	10692016	2
100237	01390082_INV	2	10692016	1
100237	01390083_INV	3	10692016	2
100237	01390083_INV	6	10692016	2
100237	01390084_INV	7	10692016	2
100237	01390084_INV	8	10692016	2
100083	01390090_INV	1	10281004	4
100083	01390090_INV	2	10692016	1
100083	01390091_INV	3	10281004	2
100083	01390091_INV	4	10281004	2
100083	01390092_INV	5	10281004	1
100083	01390092_INV	6	10281004	4
100083	01390093_INV	7	10281004	4
100237	01390089_INV	1	10692016	1

this what i am trying to get:

CustomerId	1 item for Invoice	2 items for Invoice	3 items for Invoice	4 items for Invoice	5 and more
100237	1		1	2
100083				2	2

thanks a head

[swuehl]

Maybe something like this:

Two dimensions:

CustomerId

=Aggr( Sum(Quantity),  InvoiceId)

and one expression:

=Count(DISTINCT InvoiceId)

If you are using a pivot table chart, you can pivot the second dimension to the top.

[dimak123]

i need to show only one dimension

[swuehl · Accepted solution]

Then use CustomerID as dimension and 5 expressions:

=Count({<InvoiceId = {"=Sum(Quantity)=1"}>} DISTINCT InvoiceId)

=Count({<InvoiceId = {"=Sum(Quantity)=2"}>} DISTINCT InvoiceId)

=Count({<InvoiceId = {"=Sum(Quantity)=3"}>} DISTINCT InvoiceId)

=Count({<InvoiceId = {"=Sum(Quantity)=4"}>} DISTINCT InvoiceId)

=Count({<InvoiceId = {"=Sum(Quantity)>=5"}>} DISTINCT InvoiceId)

[dimak123]

thank you

[wizardo]

Hi,

Coming late to the party but still,

in this method its hard coded, so if you have some invocies with 6 items or your max is 4?? you'll have to manualy add or remove expressions

why not use a pivot with CustomerId and Quantity (Quantity on top) as dimensions

and =Count(DISTINCT InvoiceId) as expression?

then it will be dynamic

Daniel

[swuehl]

Daniel,

I think the OP wants to have the aggregated quantitiy per invoice, so you would need a calculated Dimension (or perform the aggregation in the script).

Then you could reuse my first suggested solution in the very first answer.

[wizardo]

hmm,

i understood somthing diffrent i think.

in the oroignal post the OP states:

how many invoices(just count) contained only one item, 2 items, 3 items and 4 according to quantity field

so for example if i take customer 100237 it has 2 invoces :01390082_INV and 01390089_INV (both have 2 item according to quantity field)

so the answer as i see it is 2 (but in your way and also what appears on the original post the answer is 1)

so i admit i don't really understand.

[dimak123]

Daniel

swuehl was right,  i needed to count the invoices (for customer) that had only one item bought (in quantity),two and more.

[wizardo]

i still dont get it,

in the example i gave customer 100237 has 2(two invoices) that had only 1 item bought with Quantity of 1

not 1 as you have in your post or in the way swuehl  was counting it

so what am i missing?