topic Re: Counting based on 3 cases in QlikView

Counting based on 3 cases

dmac1971 — Fri, 12 Feb 2016 08:56:53 GMT

I have 3 separate lists of data and I want to check how many items exist on all three lists.

I thought this might work :

=aggr(count(Distinct Item) LIST)

But not getting anywhere with it? If I then wanted to count the number on specific lists, lets say only those items that appear on list 1 and 2, where the price is different and then another count where the price is the same how would you achieve it.

Re: Counting based on 3 cases

tamilarasu — Fri, 12 Feb 2016 08:59:03 GMT

Seems comma is missing.

=Aggr(count(Distinct Item), LIST)

Re: Counting based on 3 cases

MayilVahanan — Fri, 12 Feb 2016 08:59:13 GMT

Can you provide some sample data with your desired output.

Its easy to verify and resolve it

Re: Counting based on 3 cases

its_anandrjs — Fri, 12 Feb 2016 08:59:55 GMT

Can you share your qlik file.

Regards,

Anand

Re: Counting based on 3 cases

dmac1971 — Fri, 12 Feb 2016 09:01:57 GMT

Cant share Qlik file, surprised that people even ask this?

Re: Counting based on 3 cases

dmac1971 — Fri, 12 Feb 2016 09:05:02 GMT

Sorry I have a comma in my expression already, typo on here. Expression will only work when List has filter to one single List name. I need to see how many items exist on all three lists.

Re: Counting based on 3 cases

Anonymous — Fri, 12 Feb 2016 09:07:26 GMT

count(Distinct Item), LIST)+count(Distinct Item), LIST)+count(Distinct Item), LIST)

Re: Counting based on 3 cases

luciancotea — Fri, 12 Feb 2016 09:15:53 GMT

Depending on the object used to display the value, you can have different approaches. To display on a chart and see the total:

=SUM(Aggr(count(Distinct Item), LIST))

Re: Counting based on 3 cases

luciancotea — Fri, 12 Feb 2016 09:19:20 GMT

A standard support procedure is to replicate the problem. Try to recreate it in another document using dummy data.

Re: Counting based on 3 cases

dmac1971 — Fri, 12 Feb 2016 09:19:53 GMT

Lucian, many thanks, this works, I was missing the initial SUM. If I now wanted to do the same calc but base the result on another field but only if greater than zero? Say Price?

Re: Counting based on 3 cases

luciancotea — Fri, 12 Feb 2016 09:21:17 GMT

=SUM(Aggr(count(Distinct if(Price > 0, Item)), LIST))