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

[aroyi]

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

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

[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 · Accepted solution]

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]

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]

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