Remove double counting if traveler is moving between cities within same country

[wuaw]

Hi team,

I work with traveler information and need to provide a summary of total number of days traveled every month

There are requirements to remove double counting of days traveled if a traveler is moving between cities (important for trend analysis and we need to be prudent when it comes to forecasting for future months)

In the attached example:

1) Each trip is tagged with a Trip ID

2) If departure date and arrival date are the same, we need to check whether the traveler has moved between cities within the same country

(a) For example, Trip ID 1: In row 2 and 3, departure date and arrival date are the same (2-Feb-21), and traveler moved between Chicago and Seattle within USA

Hence, total number of days = 2 + 4 - 1 = 5 (required to remove 1 day of double counting)

(b) In row 3 and 4, departure date and arrival date are the same (5-Feb-21), but traveler moved between countries, hence we do not need to remove double counting. Number of days traveled in France = 4

(c) Total number of days = 5 + 4 = 9

3) For Trip ID 2 (row 5 to row 7), traveler did not move between cities within the same country. Hence, no double counting needs to be removed

Total number of days = 2 + 4 + 4 = 10

4) There is no specific requirement on how we need to present the final output (as long as we can get an answer)

The end goal through Alteryx workflow is to find out that the total number of days traveled among all travelers is 19, not 20.

Please advise how this logic can be built. Thanks!

[kelvin_law1 · Accepted solution]

Hi wuaw,

You can apply the Multi-Row Formula tool, which can build a if...then...else statement to check data between your current row and previous row.

I have attached a workflow here for your reference.  Please see if this is what you need.

[wuaw]

Hi @kelvin_law1 

Your solution is perfect, thank you!