Two sets intersection is set analysis- help?

[ioannagr]

Hi,

I want to count the customers  for both 9-12/2019 and 1-4/2020

I did this and the intersection GIVES ZERO. This is incorrect.

Year and month are master calendar fields.

count( { < Year={'2019'},Month={'Sep,'Oct','Nov','Mar'}>* < Year={'2020'},Month={'Jan','Feb','Mar','Apr'}>} distinct CustomerID)

What am I doing wrong?

[agigliotti]

let's try with the below expression:

=sum( aggr( if( count( {< Year = {'2019'}, Month = {9,10,11,12} >} distinct orderNumber ) > 0
and
count( {< Year = {'2020'}, Month = {1,2,3,4} >} distinct orderNumber ) > 0,
1, 0 ), CustomerID ) )

[tresesco · Accepted solution]

Try like:

count(  {<CustomerID=P({ < Year={'2019'},Month={'Sep,'Oct','Nov','Mar'}>})>* <CustomerID=P({< Year={'2020'},Month={'Jan','Feb','Mar','Apr'}>})>} distinct CustomerID)

[ioannagr]

Hi,

your code gives me error,

maybe i need this?

count ( {<customerId = P({< Year={'2020'},Month={'Ιαν','Φεβ','Μαρ','Απρ'}>} customerId ) >
*<customerId = P ({<Year={'2019'},Month={'Σεπ','Οκτ','Νοε','Δεκ'}>} customerId ) > }distinct customerId)

?

[ArnadoSandoval]

Hi @ioannagr 

This works:

Count(distinct {<Year={'2019'}, Month={"Sep", "Oct", "Nov", "Mar"}> + <Year={'2020'}, Month={"Jan", "Feb", "Mar", "Apr"}>} CLIENT_ID)

• The Distinct should go immediately after the parenthesis: Count(Distinct
• When you define multiple sets, their union is done with a + (plus sign)
• When defining multiple sets, their conditions go like this:  {  <first-set> + <second set> }; your first set is <Year={'2019'}, Month={"Sep", "Oct", "Nov", "Mar"}>
• Make sure your Month are defined with 3 characters, e.g. Jan, Feb, Mar, etc; (my environment returns an extra dot after each month.

Hope this helps,

[tresesco]

That is optional. Please check the opening and closing combinations of various parenthesis...that is where the issue should be, if it is there..

[ioannagr]

Seems to be working now 🙂 Thanks 😄

My next question is,  this is supposed to count CustomerIDs but when i link to sheet "IDs" , still all customerIDs show up, not only those this kpi counts.

Why is it that?

[tresesco]

---

@ioannagr wrote:

Seems to be working now 🙂 Thanks 😄

My next question is,...

---

Before coming to your next question..please mark/like the right solution. Also it's better to create a new thread for new question. If you feel this discussion reference is important for that - you could provide this thread as link there. 

If you still want to continue here - could you please explain a bit more on your issue, if possible with a sample app?