topic Including a +60 days filter using Set Analysis in QlikView

Including a +60 days filter using Set Analysis

Anonymous — Mon, 17 Feb 2014 15:49:54 GMT

Alright, the following formula does exactly what it's supposed to and I have validated the numers.

It counts the number of distinct Contacts when the [Appointment - created date] is higher than the [Campaign - Date from DM List]

Count(({<[Appointment - ID] = {'=[Appointment - Created Date]>[Campaign - Date from DM List]'}, [Appointment - Status] -={'Geannuleerd'} >} Distinct([Contact - Account #]))

My issue is that I would now like to include a max filter so that [Created date] is higher than [Campaign - Date from DM List], but not higher than 60 days.

I can make it work with "if" statements, but need to do it with Set Analysis.

Any suggestions?

Re: Including a +60 days filter using Set Analysis

Nicole-Smith — Mon, 17 Feb 2014 15:53:02 GMT

Maybe something like this:

Count(({<[Appointment - ID] = {'=[Appointment - Created Date]>[Campaign - Date from DM List]'}*{'=[Appointment - Created Date]-[Campaign - Date from DM List]<=60'}, [Appointment - Status] -={'Geannuleerd'} >} Distinct([Contact - Account #]))

Re: Including a +60 days filter using Set Analysis

Anonymous — Mon, 17 Feb 2014 16:06:51 GMT

You're freaking awesome!

Works perfectly thanks - Never thought about using the '*' 🙂

Re: Including a +60 days filter using Set Analysis

Nicole-Smith — Mon, 17 Feb 2014 16:09:58 GMT

I'm glad it works for you!