Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Capi9000
New Contributor II

Max Date, Set Analysis

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!

 

image.pngWithout selection of dates (Jan = 302)image.pngWith selection of dates (Jan = 285) and correct

1 Solution

Accepted Solutions

Re: Max Date, Set Analysis

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'
;

 

9 Replies

Re: Max Date, Set Analysis

May be try this

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

Re: Max Date, Set Analysis

Hi Sunny 

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

Re: Max Date, Set Analysis

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)
Capi9000
New Contributor II

Re: Max Date, Set Analysis

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.

Re: Max Date, Set Analysis

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

Capi9000
New Contributor II

Re: Max Date, Set Analysis

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. image.png 

Capi9000
New Contributor II

Re: Max Date, Set Analysis

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

Re: Max Date, Set Analysis

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'
;

 

Capi9000
New Contributor II

Re: Max Date, Set Analysis

@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))