Enquiry on Calculating the Count with Time Difference

[johnnyycchan]

Hi all,

There is some enquiry about the calculation on count with time difference. There is booking list as base, attendance list and summary on the count between booking month and attending month. I want to check if there is any method on calculation on count by Alteryx.

Let give some example. John Lam booked the appointment in Feb 2020 where he attended the seminar on Jan 2020. The count on booking month in Feb is 1 for 1 month before booking month.

For Ann Tsang only attended but no booked, the count was excluded. 

For Billy Cheung booked the seminar on May 2020, if he attended Apr 2020 and May 2020. The counts showed on booking month May 2020 were 2. 1 for attending on current booking month and 1 for attending on 1 month before current booking month. 

Here is the booking list, attendance list and summary on count attached for information. 

Please let us know for any furthur information required. Thanks. 

Bets Regards,

Johnny 

[apathetichell · Accepted solution]

You can join on the name, convert your dates to alteyrx dates and then use datetimediff([booking date],[attendance date],"months") to get the number of months difference between attendance and booking. There is a problem though:

as currently set up you can have one person/or one name with multiple attendance or multiple bookings and it can't really tell the difference between which booking and which attendance is paired together. If you had an identifier/primary key for which booking is related to which attendance - that would make more sense and prevent this problem.

[goyolix666]

ty