Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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' ;
May be try this
Count({<AppointmentParent = {'Nouveau RDV'}, RDV_ID = {"=Date_Key <= $(=Num(Max([Date Prise RDV])+90))"} >} RDV_ID)
Hi Sunny
Thank you for your feedback. I hadn't thought about putting the statement like that. Unfortunately it gives the same result.
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)
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.
Are you able to share a sample to see what you have?
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.
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
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' ;
@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))