topic Aggr function in QlikView

Aggr function

Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

Can someone advise where I am going wrong with this expression?

=
Count(distinct aggr(if(DateClosed>=vStartDate AND DateClosed<=vEndDate
AND floor(ShipCollectDate)>floor(DateShipment) AND [In Stock Status]='Unfulfilled' ,ShipmentId),ShipmentId))

When I apply the filter "Unfulfilled" on the [In Stock Status] field the expression calculates correctly but when this filter isn't applied it shows zeros.

Many thanks

Drew

Re: Aggr function

adamdavi3s — Thu, 18 Aug 2016 14:26:51 GMT

Hi Drew,

Its not working simply because you've got a criteria in there saying that In Stock Status must be unfulfilled for the statement to calculate

off the top of my head trya little set analysis like:

=
Count({<[In StockStatus] = {'Unfulfilled'}>} distinct aggr(if(DateClosed>=vStartDate AND DateClosed<=vEndDate
AND floor(ShipCollectDate)>floor(DateShipment) ,ShipmentId),ShipmentId))

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: Aggr function

Gysbert_Wassenaar — Thu, 18 Aug 2016 14:29:21 GMT

Remove the aggr function. You don't need it and in fact it causes the effect you see.