Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to count the sum of one metric compared to the sum of another?
Currently, this is my expression:
Count(DISTINCT {<%Order={"=QtyOrd=QtyShip"},ShipMethod={'SO'},OrderDisposition-={'W'}>} OrderNumber)
It isn't giving me the right result. I was thinking if I could compare the sum of QtyOrd to the sum of QtyShip, it might work correctly...
Thanks in advance.
You mean this?
Count(DISTINCT {<%Order={"=Sum(QtyOrd)=Sum(QtyShip)"}, ShipMethod={'SO'}, OrderDisposition-={'W'}>} OrderNumber)
You mean this?
Count(DISTINCT {<%Order={"=Sum(QtyOrd)=Sum(QtyShip)"}, ShipMethod={'SO'}, OrderDisposition-={'W'}>} OrderNumber)
A key is never a good field to use for any calculation purpose. The % as the first character of %Order indicate to me that this is a key.
I think that
Count(DISTINCT {<OrderNumber={"=QtyOrd=QtyShip"},ShipMethod={'SO'},OrderDisposition={'W'}>} OrderNumber)
might work for you...
But it depends on the fact that OrderNumber must be a unique row identificator in relation to the two fields QtyOrd and QtyShip. If not it will return a wrong count.
%Order is a key but it is also the unique identifier. Should I load another field that is %Order with a different name?
As long as it is a key it will appear in another table and then it will not be something that acts as a normal row identifier since a key field straddles two or more tables and might very well have a lot of extra values from the other tables....
So yes you will have to have some non-key row identifier to do the calculation in the search correctly if you are not using aggregates like Sunny showed you.