Can i use an expression in set analysis, or should i use an if statement?

[chriscools]

Hello,

i have an expression to make a list of only those clients which don't have an agent attached to them:

Count ( { $<[WholesaleCustomerBlocked]={"0"}  >}   if ( IsNull (WholesaleCustomerAgent),            WholesaleCustomerCode))

Now i would like to specify it more so i only see those clients without an agent but who do still have open orders,

so where   sum({<OrderStatus={"Placed", "On hold"}>}  OrderQtyOpen) is bigger than zero.

But not sure how to fit the last expression into the set analysis, if it's possible?

Should it be part of the set analysis, or should it be part of the if statement, or even a third way?

Somebody have an idea?

thanx!

grtz,

Chris

[sunny_talwar · Accepted solution]

May be this:

Count({ $<[WholesaleCustomerBlocked] = {0}, WholesaleCustomerCode = {"=Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen) > 0 and Len(Trim(WholesaleCustomerAgent)) = 0"}>} WholesaleCustomerCode)

[chriscools]

Hey Sunny,

this works, thanx!

but why did you replace the if function by Len(Trim... ?

Is it for performance or another reason?

grtz,

Chris

[sunny_talwar]

Well removing if is better, but using Len(Trim()) vs IsNull() makes a slight difference. IsNull() will exclude white spaces, but Len(Trim()) captures is pretty well

[chriscools]

If removing if is better should i also do this in a different way?

basically the table has two dimensions:

-WholesaleCustomerCode

-WholesaleCustomerFullName

Then 1 expression that you tweaked:

Count({ $<[WholesaleCustomerBlocked] = {0}, WholesaleCustomerCode = {"=Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen) > 0 and Len(Trim(WholesaleCustomerAgent)) = 0"}>} WholesaleCustomerCode)

Because of the expression i only keep the customers that don't have an agent and still have open orders.

Now i want to add 1 extra expression to show the total amount of outstanding orders:

Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen * OrderPriceUnitNet )

But if i add the expression just like that then it gives me every customer where it finds open orders for and that i don't want.

so i did it like this with an if expression:

if ( Count({ $<[WholesaleCustomerBlocked] = {0}, WholesaleCustomerCode = {"=Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen) > 0 and Len(Trim(WholesaleCustomerAgent)) = 0"}>} WholesaleCustomerCode)= 1 ,

Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen * OrderPriceUnitNet ))

but if it's better to remove if expressions then perhaps there is also another way?

grtz,

[sunny_talwar]

You can just do this:

Sum({<OrderStatus={'Placed', 'On hold'}, WholesaleCustomerCode = {"=Sum({<OrderStatus={'Placed', 'On hold'}>}  OrderQtyOpen) > 0 and Len(Trim(WholesaleCustomerAgent)) = 0"}>}  OrderQtyOpen * OrderPriceUnitNet)

[chriscools]

indeed, i didn't think it trough, but that's logical...

thanx very much!!!

grtz,

chris

[sunny_talwar]

No problem at all