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!
Solved! Go to Solution.