Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone!
I would need to calculate amount of time which salesperson spent in each country while traveling between different cities. Hardest part is that salesperson can visit one country several times. Idea is to enumerate each visit and then calculate time spent in each country. Any idea how this can be done?
Date | City | Country |
2/10/2019 | A | US |
2/11/2019 | B | US |
2/14/2019 | A | US |
2/17/2019 | A | GB |
2/20/2019 | B | GB |
2/25/2019 | C | GB |
2/27/2019 | B | GB |
2/28/2019 | A | US |
3/1/2019 | B | US |
3/3/2019 | C | US |
3/5/2019 | D | US |
3/6/2019 | B | US |
3/7/2019 | D | US |
3/8/2019 | A | GB |
3/9/2019 | B | GB |
3/14/2019 | C | GB |
3/15/2019 | A | GB |
3/17/2019 | B | GB |
Hi,
Please show the exact expected result.
Ashish, hi
Result should be as follows:
US 1 (first occurence of country) - 7 (2/10->2/17)
GB 1 - 11 (2/17->2/28)
US 2 - 8 (2/28 -> 3/08)
...
An so on
Thanks
Anyone can help? 😞
Can you confirm the calculation?
You ask for how long in each country, but there is single entries for date. A, for eaxmple:
Date | City | Country |
2/10/2019 | A | US |
2/14/2019 | A | US |
2/17/2019 | A | GB |
2/28/2019 | A | US |
are we to assume A was in the US from 2/10 - 2/17, and in GB 2/17-2/18 ?
Or is the date counted as one day, so in the example 3 days in US and 1 day in GB
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Steve, hi
You are correct. We assume that A in US was 2/10 - 2/17; GB - GB 2/17-2/28. We also assume that city A in GB is different from city A in US
Thanks
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |