Skip to Content

Unit Conversion, how to filter materials with no conversion factor

Apr 01, 2017 at 04:40 AM


avatar image

Hi guys,

I did a unit conversion in the query designer using the doc below.

This is my sample report.

The KF with NORMAL is the real keyfigure values. I mean, it's the transactional value. The other KF beside it (right side) is the converted value. The result is * since it can't add the values having different unit of measure.

If I drill down up to MATERIAL level I can see that I have 2 materials: 9DMS01240 and


Since the material 9DMS01240 doesn't have a conversion factor from KG to PAL the result was AS IS, as if nothing happened, that's why it's still in KG.

What I wanted to do is to filter the materials with no conversion factor so it won't be shown in the report or make the quantities of those materials as ZERO. Is that possible? Any idea?

This should be my FINAL result.

The material 9DMS01240 will not be included in the final result since it has no conversion factor.

How can this be done in query designer or at back end level?

Thank you.


delete.jpg (38.6 kB)
delete.jpg (23.9 kB)
delete.jpg (25.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

John Hawk Apr 18, 2017 at 09:50 PM

Consider Boolean logic

Quantity with good unit of measure = (0UNIT > 0) * SalesQty ]

John Hawk

Show 2 Share
10 |10000 characters needed characters left characters exceeded


I think it won't work since all the materials have their own unit of measure. So 0UNIT will always be greater than ZERO. Did I understand your suggestion correctly?

Thank you.



Hi Loed,

Somehow, you are able to know when a rate is "bad".

Use Boolean logic to create a test (0unit = KG) or (0Unit <> PAL) which will work.

Good luck, and let us know your final solution.

John Hawk