Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Remove the aggr function. You don't need it and in fact it causes the effect you see.
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.
Remove the aggr function. You don't need it and in fact it causes the effect you see.