problem writing set expression (exclusion)

[#1]

The idea behind the expression is:

I want to count all the products where the weight is 0 and the adjusted_weight does NOT equal 0,1.

This is the expression that i had (doesn't give the wanted result):

count({<,emptyings_weight={0}, emptyings_adjusted_weight-={'0,1'}>}product_id)

Can anyone see what is wrong?

Kind regards,

Katleen

[Gysbert_Wassenaar]

There are several issues. First you count product_id. That is a key field, so it's not clear which table should be used for the count. Never use a key field in a count, unless it's a count distinct.

Next, 0 is not 0,0. So weight={0} doesn't match any records. Use '0,0' instead.

Finally I cannot match your count of 482 with the data in the document you posted. I get a result of 2140 using this expression: count({< weight={'0,0'}, adjusted_weight-={'0,0','1,0'}>} emptyings_id ).This is the same number I get by adding all the fields in a table box object and making the selections using listboxes. First select Oudenaarde, next select 0,0 and 1,0 in the field adjusted_weight and invert the selection in that field by choosing select excluded. Finally select 0,0 in the field weight. The table box now shows 2140 records.

[jerrysvensson]

count({<product_id = E({<emptyings_adjusted_weight = {'0,1'}>} product_id), emptyings_weight={'0,0'}>} product_id)

Gives result :2181.

You need to change your data model. Can you give us the SQL code that matches the 482 result?

[#4]

My fault, I didn't make it very clear.

In the example file I simulated the result with selections.

The result should be 4630.

I checked it in SQL, You can see it in the attached file.

Kind regards,

Katleen