how to only count the values for orders higher than a certain amount?

[chriscools]

Hello,

i have an expression to give the ordered amounts against the order status as dimension

this gives me the totals

sum(OrderPriceUnitNet * OrderQty)

But for transportcosts we have a policy to only ship out those orders that are higher than 750 €, and

now i would like to make the sum of the amounts of only those orders who have a total higher than 750€

and i have the same question to count these orders, this is the expression i have to count the orders per dimension

sum(OrderPriceUnitNet * OrderQty) / count ( DISTINCT OrderNumber)

but how would i then count only those orders higher then 750€?

thanx!

chris

[lironbaram · Accepted solution]

you can use something in the light of

for counting orders:

sum(aggr(if(sum(OrderPriceUnitNet*OrderQty)>750,1,0),OrderNumber,yourDimension))

for the amount of this orders:

sum(aggr(if(sum(OrderPriceUnitNet*OrderQty)>750,sum(OrderPriceUnitNet*OrderQty),0),OrderNumber,yourDimension))

[chriscools]

Hey thank you!

for the counting of the orders it is ok that works, but for the amounts i get all zeros.

this is the formula  i use:

sum (aggr(if(sum(OrderPriceUnitNet*OrderQty)>750, sum ( OrderPriceUnitNet*orderqty),0),OrderNumber))

any idea?

i left the dimension out of the expression, because the dimension is allready in the dimensionfield of the table.

grtz,

chris

[jonathandienst]

Chris

Perhaps its just a typo in your post, but yYou seem to have field "OrderQty" and field "orderqty". QV is case sensitive for field names, so these are different fields (or "orderqty" does not exist).

Regards

Jonathan

[chriscools]

Hey Jonathan,

thanx this was indeed the case.

thanx!

chris