Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
aroyi
Contributor III
Contributor III

Multiple conditions in the same field ( intersection) in set analysis (to analyse Covid19 impact)

Hi there,

I tried to calculate the unique number of new attendees who attended our events after Covid19 started but had not attended our events prior to  Covid19. I have created a binary variable  Covid19 (Y or N) using the event date(if date<15/03/2020 'N', if (date>=15/03/2020,'Y', null()) as COVID19.

Here's the set analysis I created to capture the unique number of new attendees since COVID19:

Count({<[Contact ID]= P({<COVID19={'Y'}>} [Contact ID])> * <[Contact ID] = P({<COVID19={'N'}>}[Contact ID] )>* <[Contact ID] = P({<[Event  Status]={'Attended'}>} [Contact ID])>} Distinct [Contact ID])

There's no error in the syntax, however the number from the calculation is higher than the total number of attendance since Covid19 (it should be smaller than the total). Can someone help me to identify what's wrong with my set anlaysis? Thanks a lot!

Kind Regards,

Nina

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Like this? 

Count({<[Contact ID] = E({<COVID19 = {'N'}, [Event Status] = {'Attended'}>},COVID19 = {'Y'}, [Event Status] = {'Attended'}>} DISTINCT [Contact ID])

Excluding every Contact ID who attended prior, and select the ones attended after. 

View solution in original post

6 Replies
Saravanan_Desingh

One solution in Script.

tab1:
LOAD * INLINE [
    Contact ID, Event Status, COVID19
    1, Attended, Y
    1, Attended, N
    2, Inquired, N
    3, Mailed, N
    4, Inquired, Y
    4, Attended, Y
    5, Attended, N
];

Left Join(tab1)
LOAD [Contact ID], If(Concat(DISTINCT [Event Status])='Attended' And Concat(DISTINCT COVID19)='N', 'Y', 'N') As Flag
Resident tab1
Group By [Contact ID];

commQV32.PNG

sunny_talwar

From your if statement, it seems pre-covid19 is flagged Y and post-covid 19 is flagged N. If that is true, try this maybe

Count({<[Contact ID] = P({<COVID19 = {'N'}, [Event  Status] = {'Attended'}>}) - p({<COVID19 = {'Y'}, [Event  Status] = {'Attended'}>})>} DISTINCT [Contact ID])
Vegar
MVP
MVP

Like this? 

Count({<[Contact ID] = E({<COVID19 = {'N'}, [Event Status] = {'Attended'}>},COVID19 = {'Y'}, [Event Status] = {'Attended'}>} DISTINCT [Contact ID])

Excluding every Contact ID who attended prior, and select the ones attended after. 

Saravanan_Desingh

@Vegar  - I think you missed to close E

=Count({<[Contact ID] = E({<COVID19 = {'N'}, [Event Status] = {'Attended'}>}),COVID19 = {'Y'}, [Event Status] = {'Attended'}>} DISTINCT [Contact ID])

 

But nice learning for me 🙂

Vegar
MVP
MVP

Thanks.  I was copy-paset-typing from my 📱 and I must have missed the paranthesis while struggling to correct all autocorrect mistakes that came with it.

(I would love to se QlikScript as a separate language on my mobile keyboard together with English, Swedish, Norwegian, etc.)

@aroyi how did it go? Have we managed to solve your issue? If so then please close this thread by marking one or more replies as the correct answer(s).

aroyi
Contributor III
Contributor III
Author

Thanks so much for your help everyone,  now I have learnt how to use E() and P() in nested set analysis!😀