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

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

chriscools — Wed, 18 Nov 2020 00:15:11 GMT

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

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

MayilVahanan — Wed, 18 Nov 2020 01:40:18 GMT

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))

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

chriscools — Wed, 25 Nov 2020 23:07:31 GMT

it worked, thanx!

Chris