What's the best way to do this...

[samvile18]

...I'm trying to find the best way to count the amount of logins from my Logins table where the date is between the campaign date and 30 days after it. My data layout looks like this:

It's joined by %Master_Id but I'm getting a bit stuck with getting the set analysis to work. 

Any help would be greatly appreciated. 

[rubenmarin]

Hi, I think it will be better if you flag those records on script, you can do a join or a mapping the check each Login Date with the correspondig CampaignDate to create a binary field, then the expression could be as simple as:

Sum({<isIn30Days={1}>} [# of Logins])

[samvile18]

Hi Ruben,

Thanks very much for the reply, can you please elaborate a little more for me on the script solution?

I'm not too sure what you mean and I'm struggling to put it into practice. 

I've attached a simple example script that joins my two tables as it currently is in the app, can you have a look and let me know how you would make the changes please?

Really appreciate your reply, thank you. 

[rubenmarin]

Hi, if you alrady have the join I think you only need:

Logins:

NoConcatenate

Load *,

    If("Login Date"-CampaignDate<=30,1,0) as isLoginIn30Days

Resident LoginsTemp

order by %Master_Id, "Login Date" desc;

You can add simple table with [Login Date], CampaignDate as isLoginIn30Days fields to check the flag.

[samvile18]

I don't think I was clear enough - the tables aren't joined together, they're linked my %Master_Id...you mentioned a mapping, how would that work? 

Here's what the table layout looks like:

[rubenmarin · Accepted solution]

Right, it was a left keep, somewhat I read left join.

The mapping table could be created as:

mapCampaignDate:

Mapping LOAD %Master_Id, CampaignDate  

Resident CampaignContacts;

And then apply in the logins table as:

If("Login Date"-ApplyMap('mapCampaignDate',%Master_Id)<=30,1,0) as isLoginIn30Days

[samvile18]

This is incredibly close - thank you very much, I think I have some fine tuning to do in terms of max dates etc but this is definitely along the right lines.

Thanks for taking the time to have a look