Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi !
I’m looking for your help for this one: I’m trying to make a “last touch” analysis of our CRM campaigns.
I’m trying to create a Set Analysis that calculates the last campaign targeting a person before this person booked an certain appointment type (in this case Type A).
A simplified version of my data would look like this (in two tables):
Campaign Name | Campaign Date | Client ID |
Campaign A | 01/09/2019 | 123 |
Campaign B | 01/10/2019 | 123 |
Campaign C | 15/10/2019 | 123 |
Appointment ID | Appointment Type | Appointment Date | Client ID |
1 | Type A | 10/10/2019 | 123 |
2 | Type B | 20/10/2019 | 123 |
And the output I’m looking for would be this:
Campaign Name | Number of Appointments (Type A) |
Campaign A | 0 |
Campaign B | 1 |
Campaign C | 0 |
Where only Appointment ID 1 is calculated as it’s Type A and it relates to Campaign B as this was the latest Campaign (01/10/2019) before the person booked his appointments (10/10/2019).
I hope it makes sense and you’re able to help me – I’ve been struggling too long and now I’m giving up and asking for help.
Hello,
can you give further explainations please ?Sorry i can't get what you want exactly .
Thx
Thank you for replying Mohamed. ’ll give it a go – with fewer words 😊
I’m trying to do something like:
Count Appointment_ID
IF (Appointment Type = A)
AND
IF MAX([Campaign Date] <= [Appointment Date])
I've made it explicit that there is a client linked to each campaign and appointment as I have an idea that's why I can't get my expression to work. I've been trying with Aggr() function by Client_ID, but I can't nail it
May be something like this, refer the attachment.
Br,
KC
Hi KC
That's exactly that - I'm just trying to make it directly in the table / set expression as I will be using several variations of it.
How about this, the challenge i see is that both tables should be joined or should be brought in the UI together to do the comparison. Ref the attached.
Br,
KC
may be this ?
Use the selector to choose which type you're interested in .
I would prefer doing it using intervalmatch at script level, more robust as I do not need to worry on changing set analysis logic in the chart.
Imagine if today a business user wanna analyse by Type A, tomorrow by Type B, that seems to be an endless job.
Refer qvw attached below as reference.
Thanks and regards,
Arthur Fong
Claus, be sure to give the guys credit if any of their posts helped you get things working, you can use the Accept as Solution button on the post(s) that helped, which will also let others know what actually worked in this scenario. If you are still working on things, leave us an update.
Regards,
Brett