How can I get this result with set intersection?

[AHMB]

I'm trying to track customers who are coming through third-party channels in order to convert them to become our direct customers.

This dataset represents sales:

H stands for Third-party channel.

M stands for direct channel.

My goal is to know those customers who ONLY came to us exclusively through third-part channel between  2019 to 2021 and then from those, I want to see who has converted to exclusively direct channel in 2022.

according to the data above,  B,C,F and R were exclusively ordering through third-party channels from 2019 to 2021.

this is that expression I used to get that:

concat (aggr(only({$<Year={">=2019<=2021"},Customer=E({<SOURCE-={H},Year={">=2019<=2021"}>})>} distinct Customer),Customer),',')

Now in 2022, from that group of customers, only C has converted to ordering exclusively directly. 

I tried to get the expression to return only C but I could not.   below is my attempt:

concat (distinct {$
<Year={">=2019<=2021"},Customer=E({<SOURCE-={H},Year={">=2019<=2021"}>})> 
* 
<Year={"2022"},Customer=E({<SOURCE={H},Year={"2022"}>})>} Customer,',')

and here is the result:

here is the data:

LOAD * INLINE [
    Year, Customer, SOURCE
    2019, A, H
    2019, A, M
    2019, R, H
    2019, B, H
    2019, C, H
    2020, A, H
    2020, D, M
    2020, C, H
    2020, F, H
    2021, E, M
    2021, C, H
    2021, B, H
    2022, C, M
    2022, B, H
    2022, F, H
    2022, C, M
    2022, R, M
    2022, R, H
    2022, S, H
    2022, Q, M
];

I appreciate your help

[Digvijay_Singh]

May be try these - 

1. Concat(distinct{<Customer=E({<SOURCE={M},Year={">=2019<=2021"}>})>}Customer,',') 

2. Concat(distinct{<Customer=E({<SOURCE={M},Year={">=2019<=2021"}>})*E({<SOURCE={H},Year={2022}>})>

}Customer,',')

[AHMB]

I tried both expressions:

 expression 1 returns B,C,F,Q,R,S

expression 2 returns C,Q

both are not the desired results.   It should return only C. 

[marcus_sommer · Accepted solution]

You may try it with multiple conditions-level which are working on top of each other. This could mean to use conditions within the inner and an outer aggregation, Maybe something like this:

concat({<Year={"2022"},Customer=E({<SOURCE={H},Year={"2022"}>})>}
   aggr(
     only({$<Year={">=2019<=2021"},
             Customer=E({<SOURCE-={H},Year={">=2019<=2021"}>})>} 
     distinct Customer),
   Customer),
',')

- Marcus

[AHMB]

Thank you Marcus, It seems to be working.

may I say, I'm still interested to know why the original expression did not work. 

[marcus_sommer]

I'm not absolutely sure but I think the intended intersection couldn't work with:

sum({ condition1 * condition2 } value)

because both conditions exclude each other. This means within a one-level approach you would need to include condition2 within condition1 and therefore in the end applying a single-condition (even if they have multiple sub-conditions which are connected with any operators). I assume that this would be also possible in your case but I believe the logically complexity would be quite high and probably much higher as using a multi-level condition, like the above approach.

- Marcus