topic Re: Count Quantity for invoice in QlikView

Count Quantity for invoice

dimak123 — Thu, 28 Jan 2016 13:07:45 GMT

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

Re: Count Quantity for invoice

swuehl — Thu, 28 Jan 2016 20:24:30 GMT

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.

Re: Count Quantity for invoice

dimak123 — Sun, 31 Jan 2016 05:55:30 GMT

i need to show only one dimension

Re: Count Quantity for invoice

swuehl — Sun, 31 Jan 2016 13:18:02 GMT

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)

Re: Count Quantity for invoice

dimak123 — Sun, 31 Jan 2016 13:27:29 GMT

thank you

Re: Count Quantity for invoice

wizardo — Thu, 11 Feb 2016 10:34:07 GMT

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

Re: Count Quantity for invoice

swuehl — Thu, 11 Feb 2016 10:42:03 GMT

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.

Re: Count Quantity for invoice

wizardo — Thu, 11 Feb 2016 11:02:58 GMT

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.

Re: Count Quantity for invoice

dimak123 — Thu, 11 Feb 2016 11:42:06 GMT

Daniel

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

Re: Count Quantity for invoice

wizardo — Thu, 11 Feb 2016 12:33:27 GMT

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?

Re: Count Quantity for invoice

swuehl — Thu, 11 Feb 2016 12:36:37 GMT

You need to aggregate per invoice, so 01390082_INV has two lines, sum of quantity is (2+1)=3.

Re: Count Quantity for invoice

dimak123 — Thu, 11 Feb 2016 12:39:33 GMT

Customer 100237 had 4 invoices (sum the lines by invoice):

01390082_INV - have 3 in quantity
01390083_INV - have 4 in quantity
01390084_INV - have 4 in quantity
01390089_INV - have 1 in quantity