topic How can I get this result with set intersection? in App Development

How can I get this result with set intersection?

AHMB — Wed, 09 Feb 2022 20:26:01 GMT

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

Re: How can I get this result with set intersection?

Digvijay_Singh — Thu, 10 Feb 2022 02:46:30 GMT

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,',')

Re: How can I get this result with set intersection?

AHMB — Thu, 10 Feb 2022 04:15:26 GMT

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.

Re: How can I get this result with set intersection?

marcus_sommer — Thu, 10 Feb 2022 10:43:35 GMT

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

Re: How can I get this result with set intersection?

AHMB — Thu, 10 Feb 2022 12:58:18 GMT

Thank you Marcus, It seems to be working.

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

Re: How can I get this result with set intersection?

marcus_sommer — Thu, 10 Feb 2022 15:23:14 GMT

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