topic Re: Count first instance of after a given date(based on another field) in QlikView

Count first instance of after a given date(based on another field)

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have two tables:

1 shows events which occurred

2 shows the number of activities and related details.

I want to create a flag which I will count to say if an activity occurred after an event.

e.g if there was an event occurred 12.06.18 and an several activities occurred post 12.06.18 e.g. 1 activity on 13.06 and 2 activities on 14.06 then I just want to identify these but ultimately count them as just 1. I've been trying a flag in the script and then count distinct in the front end but not getting what I want.

There could be multiple events and I don't want to count activities before an event has happened.

I will also want to understand difference between dates like event and first activity after event.

Thanks

Re: Count first instance of after a given date(based on another field)

isorinrusu — Wed, 13 Jun 2018 11:39:00 GMT

Hi Kwok,

Could I see your data model? I need to know how the activities and events are associated.

Regards,

Sorin.

Re: Count first instance of after a given date(based on another field)

alwayslearning — Wed, 13 Jun 2018 11:53:50 GMT

Hi,

They are associated by an Account ID

Table 1: Account Table

Account ID

Table 2: Activity
Account ID	Activity Name	Activity Date
1	Test	12.06
1	Example	14.06
2	Test	12.06
3	Example	10.06

Table 3: Event
Account ID	Event	Event Date
1	Event 1	13.05
1	Event 2	13.06
2	Event 1	13.06

This is a sample of the 3 tables.

In the above you see Account 1 had two events and both had activities post event so that would both be counted.

Account 2 had one event but no activities for this account happened after this event. So I don't want to count this one.

(The activity table does not necessarily have a direct correlation with an event so there is no activity ID which directly links to an event ID. So that is why I want to use post activity after event)

Re: Count first instance of after a given date(based on another field)

isorinrusu — Wed, 13 Jun 2018 13:13:48 GMT

Hi,

I'm not sure what you mean when you say that the Event table is not related to the Activity table. If they're not related, what dates do you want to compare?

Regards,

Sorin.

Re: Count first instance of after a given date(based on another field)

alwayslearning — Wed, 13 Jun 2018 13:35:58 GMT

Hi Sorin,

I want to compare Event Date vs Activity date. It is related via the account but nothing more than that. So I mean you cannot say that this activity was directly related to this event. (More of the analysis issue to solve rather than data issue which I'm trying to solve here)

What I am looking to achieve is find first activity date after an event for every event.

Thanks