topic Re: Count items for invoice in QlikView

Count items for invoice

dimak123 — Thu, 28 Jan 2016 10:30:55 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.

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

Re: Count items for invoice

MayilVahanan — Thu, 28 Jan 2016 10:41:44 GMT

Try like this

Customer, Invoice Id as dimension

Count(distinct ItemId) as Measure

So that you can able to find, each invoices has how many item count.

Re: Count items for invoice

dimak123 — Thu, 28 Jan 2016 10:43:34 GMT

i need to show i my final table only clients and counts , without invoices

Re: Count items for invoice

MayilVahanan — Thu, 28 Jan 2016 10:50:16 GMT

Could you please say the output how you expected for the sample data.

Re: Count items for invoice

dimak123 — Thu, 28 Jan 2016 10:59:24 GMT

my expected result
CustomerId	1 item for Invoice	2 items for Invoice	3 items for Invoice	4 items for Invoice
100237	10	5	4	1
100083	15	7	2	1

Re: Count items for invoice

PradeepReddy — Thu, 28 Jan 2016 11:54:38 GMT

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

Re: Count items for invoice

dimak123 — Thu, 28 Jan 2016 11:58:21 GMT

thank you, but the itemid doesn't really matter only the Quantity field

Re: Count items for invoice

PradeepReddy — Thu, 28 Jan 2016 12:18:53 GMT

how you are calculating '1 item for Invoice' as 10 for customer ID=100237?

Re: Count items for invoice

dimak123 — Thu, 28 Jan 2016 12:20:47 GMT

the numbers i put its not real

just the structure

Re: Count items for invoice

sasikanth — Thu, 28 Jan 2016 12:24:54 GMT

hi ,

please find the attachment ,

seems your expected output is wrong according to the sample data

Re: Count items for invoice

Thu, 28 Jan 2016 12:36:29 GMT

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

Re: Count items for invoice

tresB — Thu, 28 Jan 2016 12:46:00 GMT

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)

....

Re: Count items for invoice

sasikanth — Thu, 28 Jan 2016 18:24:45 GMT

hi,

Did you check my attachement ?

Re: Count items for invoice

PradeepReddy — Fri, 29 Jan 2016 04:38:22 GMT

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