set analysis: exclude 2 values doesn't include all minus the 2 values

[chriscools]

Hello,

i need to separate a salesamount by 2 groups of customers:

group 1 = sum(  {< WholesaleCustomerSegmentationLevel1 = {"Shop-in-Shop","eigen winkels"} >}  OrderPriceUnitNet * OrderQty)

and group 2 is everything excluded by the first group and i added a minus to the set analysis like this:

sum(  {< WholesaleCustomerSegmentationLevel1 -= {"Shop-in-Shop","eigen winkels"} >}   OrderPriceUnitNet * OrderQty)

but when i subtract these 2 numbers it's less than the total amount.

I found out it comes from the field "WholesaleCustomerSegmentationLevel1" which is empty for some customers.

Can i adapt the set analysis to give me anything excluding group 1 also taking into account the empty fields for those customers?

thanx!

grtz,

chris

[swuehl · Accepted solution]

Maybe like

= sum(  {$ - < WholesaleCustomerSegmentationLevel1 = {"Shop-in-Shop","eigen winkels"} >}  OrderPriceUnitNet * OrderQty)

[swuehl]

Or create a field modifier on a key field using e() function:

Excluding values in Set Analysis

Maybe like

sum(  {< OrderID = e({<WholesaleCustomerSegmentationLevel1 = {"Shop-in-Shop","eigen winkels"} >}  ) >} OrderPriceUnitNet * OrderQty)

[chriscools]

thanx, this worked perfect!!

i'll also look into your tip using the e() function

grtz