Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'.
@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.