Max Date, Set Analysis

[Anonymous]

Hi! 

I've been fighting with this one for a while, but now I give up and ask for your help 🙂 

As an example I have some marketing campaigns that I'm running every month (Direct mails to database). I know the month of the campaigns and now I would like to know the effect of them (effect is here measured as the number of appointments!). A person on the database might be targeted with a new campaign 6 months later so I would like to add an "end" to when I attribute the effect the a campaign. Let's say if you react within 90 days of the mailing I'll attribute that to the mailing. 

I've succeeded in creating a measure that takes account for a max date:

count({<AppointmentParent={'Nouveau RDV'}, 
Date_Key = {"=Date_Key<=$(=num(max([Date Prise RDV])+90))"}
 >} RDV_ID)

The correct result I'm looking for is 285, but I'm getting 302. UNLESS I select the available campaign dates (Date Prise RDV) in January, then I get the correct number of 285. 

Any ideas to how I can account for it in the measure? Not sure if it's the write wording, but am I somehow missing a "Group By" clause per client or campaign date? 

Any help welcome, thank you!

Without selection of dates (Jan = 302)With selection of dates (Jan = 285) and correct

[sunny_talwar]

May be try this

Count({<AppointmentParent = {'Nouveau RDV'}, 
RDV_ID = {"=Date_Key <= $(=Num(Max([Date Prise RDV])+90))"}
 >} RDV_ID)

[Anonymous]

Hi Sunny 

Thank you for your feedback. I hadn't thought about putting the statement like that. Unfortunately it gives the same result. 

[sunny_talwar]

I think the Dollar sign expansion is causing this issue... seems like you need something like this

Count({<
  AppointmentParent = {'Nouveau RDV'}, 
  RDV_ID = {"=Date_Key - 90 <= Aggr(Max(TOTAL <Campaign> [Date Prise RDV]), RDV_ID, Campaign)"}
>} RDV_ID)

[Anonymous]

Thanks Sunny 

I'm ending up with the same result. Do you reckon it's a matter of my data structure? I'm wondering if it's really solvable with an expression. 

I can group per client (PER_ID) as well, but that doesn't give me the result I'm looking for either.

[sunny_talwar]

Are you able to share a sample to see what you have?

[Anonymous]

I'll try if I can grab the data out, I understand it's not easy to help without it 🙂 

I've dug a bit into the records and seem to have identified when I encounter the issue. It's basically when someone has booked an appointment both within the perimeter (within 90 days) and then again after +90 days. What happens is that two appointments are counted. If both Appointments where within I would like to count them twice, but not when it outside. 

Attached screenshot maybe help explain. Below a client that has booked 3 appointments. Here it is calcualted correctly with only 2 appointments, but if I don't have any filters applied it will calculate 3 appointments.  

[Anonymous]

I've added a file with data including a detached table showing the result I'm aiming for = 285. (Except that I also need to include a minimim date > "Date Prise RDV", but that one I think managed to solve 🙂

Thank you for your help

[sunny_talwar · Accepted solution]

Try this

=Sum({<AppointmentParent={'Nouveau RDV'}>}Aggr(If(Date_Key <= Max(TOTAL <Campaign, [MonthYear Prise RDV]> Aggr(Max([Date Prise RDV]) + 90, Campaign, [MonthYear Prise RDV], RDV_ID, Date_Key)), 1, 0), Campaign, [MonthYear Prise RDV], RDV_ID))

Where [MonthYear Prise RDV] is created in the script like this

LOAD Campaign, 
     [Date Prise RDV], 
     PER_Code,
	 Date#(Year([Date Prise RDV]) & '-' & Month([Date Prise RDV]), 'YYYY-MMM') as [MonthYear Prise RDV]
FROM
[C:\Users\cbe\Desktop\QV Eks\Campaign.xlsx]
(ooxml, embedded labels)
Where Campaign = 'PF : campagne suspects MDN1'
;

[Anonymous]

@sunny_talwar you're brilliant, wow! Thank you very much. This has really helped me forward (and improve my understanding of QV). Thank you 🙂 

I've added the minimum as well using your logic and I getting the right result. 

=Sum({<AppointmentParent={'Nouveau RDV'}>} Aggr(
If(Date_Key <= Max(TOTAL <Campaign, [MonthYear Prise RDV]> Aggr(Max([Date Prise RDV]) + 90, 
Campaign, [MonthYear Prise RDV], RDV_ID, Date_Key)),
If(Date_Key >= Min(TOTAL <Campaign, [MonthYear Prise RDV]> Aggr(Min([Date Prise RDV]), 
Campaign, [MonthYear Prise RDV], RDV_ID, Date_Key))
, 1, 0))
, Campaign, [MonthYear Prise RDV], RDV_ID))