Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i have this expression to calculate the total amount of outstanding orders for a client but only making the sum for the orders where the amount is higher then 750 €.
sum(aggr(if(sum(OrderPriceUnitNet*OrderQtyOpen)>750, sum ( OrderPriceUnitNet*OrderQtyOpen),0),OrderNumber))
this is allready ok.
but i want also to have the same total, but not looking at an order but looking at the total of the client.
for example in the expression above orders for example with 400€ or 500€ open are not counted, but in fact i'll have to look at it in total per client and not on orderlevel.
as long as the client gets to his 750 € (doesn't matter on how many orders) goods can get shipped out.
it thought it would be as simple as changing the field "ordernumber" by the field "WholesalesCustomerFullName", but that doesn't work...
can someone help me with this?
i'm including the file with the table i'm working on.
in the included example there are 3 orders that have a total of +/- 790€, so i want that counted in the the column "orders higher than 750€
thanx!
chris
Chris,
if you want to count the customers, maybe like this:
=count(DISTINCT {<WholesaleCustomerFullName = {"=sum(OrderPriceUnitNet*OrderQtyOpen)>750"}>} WholesaleCustomerFullName)
or if you want to count the orders for these customers:
=sum({<WholesaleCustomerFullName = {"=sum(OrderPriceUnitNet*OrderQtyOpen)>750"}>} 1)
Hope this helps,
Stefan