count all skucodes in textobject with aggregation and set analysis: wrong total

[chriscools]

Hello,

i would like to do a count of all Sku codes with these 3 conditions:

1 = "info" equals Active or Liquidation

2 = "TypeArtikel equals Normaal

3 = but also  where the sum of [AK # open] + sum ( {<warehouse_name = {"WHS BE"}>}qty - sum ( qty_unit_open ) for each sku-code is less than zero

And i don't know where to place that last part.

I have this formula:

=count ( {<info = {'Active','Liquidation'}, TypeArtikel={'Normaal'} >} ( (Aggr( (sum ([AK # open])+sum ( {<warehouse_name = {"WHS BE"}>}qty)-sum ( qty_unit_open )),sku_code)<=0)))

But then it only gives me the number of all sku_codes for the first 2 conditions, but it doesn't take into account that the aggregated sum must be less than zero.

Any idea on what i might do to fix this?

htanx!

Chris

[MayilVahanan · Accepted solution]

HI @chriscools 

Try like below

Sum(Aggr(If(Sum({<info = {'Active','Liquidation'}, TypeArtikel={'Normaal'} >} [AK # open] )+ sum ( {<warehouse_name = {"WHS BE"},info = {'Active','Liquidation'}, TypeArtikel={'Normaal'}>}qty) - sum ({<info = {'Active','Liquidation'}, TypeArtikel={'Normaal'} >}  qty_unit_open )<=0, 1, 0), sku_code))

[chriscools]

it worked, thanx!

Chris