Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Last touch analysis (CRM Campaign Analysis)

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.

Labels (1)
8 Replies
mohamed_ahid
Partner - Specialist
Partner - Specialist

Hello,

can you give further explainations please ?Sorry  i can't get what you want exactly .

 

Thx

Anonymous
Not applicable
Author

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

jyothish8807
Master II
Master II

May be something like this, refer the attachment.

 

Br,

KC

Best Regards,
KC
Anonymous
Not applicable
Author

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. 

jyothish8807
Master II
Master II

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

Best Regards,
KC
mohamed_ahid
Partner - Specialist
Partner - Specialist

may be this ?

Use the selector to choose which type you're interested in .

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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.

clipboard_image_0.png

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

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.