Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
ioannagr
Creator III
Creator III

Two sets intersection is set analysis- help?

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?

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

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

View solution in original post

7 Replies
agigliotti
Partner - Master III
Partner - Master III

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
MVP
MVP

Try like:

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

ioannagr
Creator III
Creator III
Author

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
Specialist II
Specialist II

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,

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
tresesco
MVP
MVP

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
Creator III
Creator III
Author

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
MVP
MVP


@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?