Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
dmelillo
Contributor III

Count Activities Where ActivityDate > ResponseDate

I have a data that looks like in CAMPAIGN RESPONSE TABLE:

Id    Name        CreateDate
001  Tom Cruise   2018-08-29
002  Tom Hanks    2018-09-02
003  Bill Nye     2018-06-25
004  Johnny Cash  2018-06-27


I also have a data that looks like in ACTIVITY TABLE:


Id   PersonId  Name        ActivityDate
117  001       Tom Cruise  2018-08-30
118  001       Tom Cruise  2018-09-31
211  003       Bill Nye   2018-06-26
212  003       Bill Nye   2018-06-27


I want to create a STRAIGHT TABLE that shows ALL CAMPAIGN RESPONSES and if they have had an ACTIVITY since the CreateDate. Something like:


Id   Name       CreateDate ActivitesAfterResponse
001  Tom Cruise  2018-08-29   2
002  Tom Hanks   2018-09-02   0
003  Bill Nye    2018-06-25   2  
004  Johnny Cash 2018-06-27   0



Currently, I have set analysis like below but it is not working:


=count({<ActivityDate={">CreateDate"}>}distinct(ActivityId))


Any help is appreciated! Thank you!!

2 Replies
Partner
Partner

Re: Count Activities Where ActivityDate > ResponseDate

Hi Dave,

That will not work with set analysis when it's a variable date.

Set analysis will only calculate once, and so, its best to make the validation throught the script, rather than the graphs.

First, do a simple transformation like bellow:

Created:

load * Inline

[

Id,Name,CreateDate

001,Tom Cruise, 2018-08-29

002,Tom Hanks,2018-09-02

003,Bill Nye, 2018-06-25

004,Johnny Cash,2018-06-27

];

Activity:

Load * Inline

[

Id,PersonId,Name,ActivityDate

117,001, Tom Cruise,2018-08-30

118,001, Tom Cruise,2018-09-31

211,003, Bill Nye, 2018-06-26

212,003, Bill Nye, 2018-06-27

];

left join (Created)

Load

Name,

ActivityDate

Resident Activity;

drop table Activity;

FinalData:

Load

Id,

Name,

CreateDate,

ActivityDate,

if (ActivityDate>CreateDate,1,0) as [Flag]

Resident Created;

drop table Created;

And as epxression, use the flag as your aggregation field.

sum(Flag)

That will get you:

sample.png

Attached the QVW i used.

andrey_krylov
Valued Contributor

Re: Count Activities Where ActivityDate > ResponseDate

Hi, Dave. Felip is right, set analysis is not appropriate to this challenge. But you can try to solve it by expression as well

Sum(Aggr(If(ActivityDate > Max(TOTAL <Name> CreateDate), 1), Name, Id))