Count distincvt if 3 conditions are met in set analysis

[Anonymous]

I would like to know how many unique "Service_Order_Number" exist if the "expense_type' = labor, the "...type_description" = "travel_time", and the the amount of travel time ("invoiced_qty...") is equal to zero. Basically, unique service calls that have no travel time invoiced against them.  I get no returns? =COUNT({$}DISTINCT Service_Order_Number)

[Anonymous]

Sorry, full expression - =COUNT({$}DISTINCT Service_Order_Number)

[pover]

Try this...

=count({$<exprense_type={'labor'},type_description={'travel_time'}, Service_Order_Number={"=sum(invoiced_qty)=0"}>} Distinct Service_Order_Number)

Karl

[Anonymous]

Cannot seem to post the whole expression unless I type it in manually...

=COUNT({$<Expense_Type={"Labor"}, Service_Expense_Type_Description = {"Travel Time"}, "Invoiced_Quantity" = {"0"}>}DISTINCT Service_Order_Number)

[Anonymous]

That is very helpful Karl, thank you.  It brings to light another issue that I would like to accomodate for - The exporession works great when a Service Order HAS a labor line with description travel, but in reviewing the results, I think what I really want is a countof unique service orders where there are NO service expense type descriptions "travel time"

[pover]

Just put a '-' in front of the equal sign to exclude that value.

=count({$<exprense_type={'labor'},type_description-={'travel_time'}, Service_Order_Number={"=sum(invoiced_qty)=0"}>} Distinct Service_Order_Number)

Karl

[Anonymous]

Getting closer!  If the Service Order has NO expense type description "travel.." count unique Service orders.

[pover]

Instead of 'travel_time' put "travel*"

Karl

[pover · Accepted solution]

Sorry I meant expense_type -= {"travel*"}

[Anonymous]

Karl, thank you so much for your help.  I am so close.  It is counting now only when both expense type = travel = 0  and all other expense type = 0.  I only wat to count unique when only expense type description -= travel.  I would send you the qvw, but it is huge.