Set Analysis with dates "greater than"

[Anonymous]

Hi  

I'm trying to use set analysis to count the number of appointments, but only when the date of the appointment (Appointment - Event Date) is higher than the campaign date (Campaign - Date from DM List). I have tried different approaches, but without any luck. Is it even possible to use Set Analysis for this type of analysis?

Count

({<([Appointment - Event Date]-[Campaign - Date from DM List]) = {'>0'}>} [Appointment - ID])

Count

({<[Appointment - Event Date] = {'>[Campaign - Date from DM List]'}>} [Appointment - ID])

Any ideas?

Message was edited by: Claus Beck

[swuehl]

Ìf you have have only unique Appointment - Event Dates and the Campaign dates are located in the same table, try

Count ({<([Appointment - Event Date] = {"=[Appointment - Event Date]  > [Campaign - Date from DM List]"}  >} [Appointment - ID])

But this should be not much better than

Count (if( [Appointment - Event Date] > [Campaign - Date from DM List], [Appointment - ID]) )

[preminqlik]

Hi claus,ty this

create one flag in backend

if(Appointment - Event Date>Campaign - Date,1,0)                              as                    flag,

and in now front end use the following expression

count({<flag={1}>}Dstinct [Appointment-ID])

regards

prem

[CELAMBARASAN]

You could use the below expression

Count({<[Appointment - ID] = {"=[Appointment - Event Date]>[Campaign - Date from DM List]'}>} [Appointment - ID])

[preminqlik]

for distinct

Count({<[Appointment - ID] = {"=[Appointment - Event Date]>[Campaign - Date from DM List]'}>} DISTINCT  [Appointment - ID])

[#6]

Use regular IF inside COUNT

COUNT( DISTINCT IF([Appointment - Event Date] > [Campaign - Date from DM List], [Appointment - ID]))