Count Sum

[lfholland]

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.

[sunny_talwar · Accepted solution]

You mean this?

Count(DISTINCT {<%Order={"=Sum(QtyOrd)=Sum(QtyShip)"}, ShipMethod={'SO'}, OrderDisposition-={'W'}>} OrderNumber)

[petter]

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.

[lfholland]

%Order is a key but it is also the unique identifier.  Should I load another field that is %Order with a different name?

[petter]

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.