10 Replies Latest reply: Dec 27, 2011 8:50 PM by Jonathan Love

# Count distincvt if 3 conditions are met in set analysis

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)

• ###### Count distincvt if 3 conditions are met in set analysis

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

• ###### Count distincvt if 3 conditions are met in set analysis

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)

• ###### Re: Count distincvt if 3 conditions are met in set analysis

Try this...

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

Karl

• ###### Count distincvt if 3 conditions are met in set analysis

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"

• ###### Count distincvt if 3 conditions are met in set analysis

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

• ###### Count distincvt if 3 conditions are met in set analysis

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

• ###### Count distincvt if 3 conditions are met in set analysis

Karl

• ###### Count distincvt if 3 conditions are met in set analysis

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

• ###### Count distincvt if 3 conditions are met in set analysis

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.

• ###### Count distincvt if 3 conditions are met in set analysis

Actually it works - thanks again