Calculate distinct count by group

[sam1508]

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.

[hic]

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'.

[Blalock69]

---

@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.