Set analysis modifier for conditional exclusion

[miyako_FLO]

Hi!

We have columns T_Seller_Country and T_Buyer_Country, and I want to calculate the Seller_Volumes of the records of which Buyer_Country is not equal to the Seller_Country (i.e. sum of all the other countries volumes than one's own country's). My expression is something as follows:

Sum({$<T_Seller_Country-=T_Buyer_Country>}T_Seller_Volume)

When I insert an actual country value to T_Buyer_Country, the expression works.

How can I reference the value stored in a column so to excluding the value corresponding to the records in another column?

Thank you for your help!

Miyako

[ggijben · Accepted solution]

Hi @miyako_FLO ,

This is not how Set Analysis works unfortunately. Set Analysis is evaluated once for a chart and it will not regard the current dimension value for each row. So it doesn't calculate the "T_Buyer_Country" value for each row, but only once for the whole chart. And since there are multiple "T_Buyer_Countries" in the chart, it goes wrong.

Im not sure how your datamodel looks like, but you could create a extra column in the script with the following formula: 

IF( T_Seller_Country <> T_Buyer_Country, 1, 0) AS T_Seller_Volume_Flag

Then you could use the following measure:

Sum({$< T_Seller_Volume_Flag = {1}  >}T_Seller_Volume)

[miyako_FLO]

Thank you very much Ggijben! It worked out!

Miyako