topic Re: Calculate distinct count by group in QlikView

Calculate distinct count by group

sam1508 — Wed, 09 Feb 2022 05:16:11 GMT

Hello all,

I have a table in which I have multiple records for the same Commodity with different status. I would like to write an expression in which I want to count number of Commodities with 'InProgress' status. The record should get counted only if the status is not Completed for the same commodity. There may be additional records for the same Commodity with another Status (Void) but I don't have to account for that.

For below table, the count of Commodities of InProgress is 2.

Commodities	Sub Commodity	Status
Comm1	SC1	Void
Comm1	SC2	InProgress
Comm1	SC3	InProgress
Comm2	SC1	Void
Comm2	SC2	Complete
Comm3	SC1	Complete
Comm3	SC2	InProgress
Comm4	SC1	InProgress

Comm1 doesn't have Complete Status so it should get counted as 1 for InProgress

Comm2 doesn't have InProgress status so it won't get counted.

Comm3 has Complete and InProgress both so we don't need to count as it is already Completed

Comm4 has InProgress so we need to count it as 1 So, total InProgress Status count for the below table is 2.

Hope this example helps and this is the expression which I came up -

=Count({<Region=>} Aggr(If( Status = 'InProgress' and Status <> 'Complete' , Status), Commodities, Commodities))

I also want Region filter not to get applied but that is also not working.

Re: Calculate distinct count by group

hic — Wed, 09 Feb 2022 08:39:03 GMT

Try

Count({<Region=,Commodities=E({<Status= {'Complete'}>} Commodities)*P({<Status= {'InProgress'}>} Commodities)>} distinct Commodities)

The E()-function returns values of "Commodities" that don't have status 'Complete'.

The P()-function returns values of "Commodities" that have status 'InProgress'.

Re: Calculate distinct count by group

Blalock69 — Thu, 17 Feb 2022 04:07:34 GMT

@hic wrote: myccpay

Try

Count({<Region=,Commodities=E({<Status= {'Complete'}>} Commodities)*P({<Status= {'InProgress'}>} Commodities)>} distinct Commodities)

The E()-function returns values of "Commodities" that don't have status 'Complete'.

The P()-function returns values of "Commodities" that have status 'InProgress'.

Thank you for the reply, My query has been solved.