topic Re: Count based on condition in QlikView

Count based on condition

Anonymous — Wed, 06 Jul 2016 15:04:46 GMT

Hi guys,

I have a database with Subsidiaries, Shipments and Customers. It is possible that 1 shipment goes to more than 1 customer (milk-run) and I would like to display that. Therefore, I want to count the number of shipments which have more (or equal to) than 2 customers for a subsidiary. In the end, I'd like to have a simple table in QlikView in the following format:

Subsidiary	Shipments >= 2 Customers
A	5
B	8
C	3

Any ideas?

Thanks!

Re: Count based on condition

sinanozdemir — Wed, 06 Jul 2016 16:06:41 GMT

Hi,

Maybe something like this:

Count({<Subsidiary={"Aggr(Count(Distinct Customers), Subsidiary)>=2"}>} Distinct Shipments)

Or you can post sample data which makes it easier to come up with a working syntax.

Thanks

Re: Count based on condition

sunny_talwar — Wed, 06 Jul 2016 16:32:27 GMT

I would create a new field in the script which combines Subsidiary and Shipment field and use that in my set analysis condition

LOAD Customers,

Subsidiaries,

Shipments,

AutoNumber(Subsidiaries&Shipments, 'Key1') as Key

FROM Source....

and then try this expression:

Count(DISTINCT {<Key = {"=Count(DISTINCT Customers) > 1"}>}Shipments)

Re: Count based on condition

Anonymous — Thu, 07 Jul 2016 07:52:57 GMT

Hi Sinan,

I have attached my database. Preferably, I would like to have the SUB as a Dimension in the Pivot Table and the count as an expression.

Thanks for your help.

Re: Count based on condition

sinanozdemir — Tue, 12 Jul 2016 13:10:37 GMT

Hey Radu,

Please the attached and let me know if this comes close to what you are looking for.

Thanks

Re: Count based on condition

Anonymous — Tue, 12 Jul 2016 14:54:00 GMT

Hi Sinan,

That is what i am looking for but for some reason it does not count properly. If you look at Subsidiary A, we see 2 shipments with more than 2 customers but that is not true if you check the raw data. Subsidiary A should have 0 shipments with more than 2 customers (or equal).

Thanks,

Radu

Re: Count based on condition

sinanozdemir — Tue, 12 Jul 2016 15:27:32 GMT

Let's see if this one is correct:

Thanks