Aggr function

[#1]

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

[adamdavi3s]

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.

[Gysbert_Wassenaar · Accepted solution]

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