topic Re: Multiple conditions in the same field ( intersection) in set analysis (to analyse Covid19 impact in App Development

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

aroyi — Wed, 08 Jul 2020 23:10:25 GMT

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

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

Saravanan_Desingh — Wed, 08 Jul 2020 01:37:44 GMT

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];

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

sunny_talwar — Wed, 08 Jul 2020 02:54:22 GMT

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])

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

Vegar — Wed, 08 Jul 2020 07:31:52 GMT

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.

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

Saravanan_Desingh — Wed, 08 Jul 2020 13:09:02 GMT

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

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

Vegar — Wed, 08 Jul 2020 17:00:24 GMT

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).

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

aroyi — Wed, 08 Jul 2020 23:29:07 GMT

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