Forum Discussion
Traffic Flow - Rank and Counts
Hi all! I've got a funny question I can't quite wrap my mind around. I have a data set with a user, a date/time, and a location. I want to create an idea of traffic flow per day per user, so that I can see where they visited first, second, third, fourth, etc. Repeated visits to the same place consectively shouldn't count, but repeat visits non-consecutively should. Here's an example of what I'm trying to do, in the 'Result' column (and here's the data set):
| date | time | user_id | loc | Result |
| 8/21/2021 | 9:00 AM | Paul | Schwartz Building | 1 |
| 8/21/2021 | 10:00 AM | Joe | Peterson Wing | 1 |
| 8/21/2021 | 10:01 AM | Paul | Peterson Wing | 2 |
| 8/21/2021 | 11:00 AM | Paul | Cafeteria | 3 |
| 8/21/2021 | 11:05 AM | Joe | Science Lab | 2 |
| 8/21/2021 | 11:30 AM | Sally | Science Lab | 1 |
| 8/21/2021 | 12:00 PM | Joe | Peterson Wing | 3 |
| 8/21/2021 | 1:00 PM | Joe | Parking Garage | 4 |
| 8/21/2021 | 2:35 PM | Sally | Science Lab | 1 |
| 8/21/2021 | 4:00 PM | Sally | Cafeteria | 2 |
| 8/21/2021 | 9:00 AM | Sally | Peterson Wing | 3 |
| 8/21/2021 | 10:00 AM | Paul | Cafeteria | 3 |
| 8/21/2021 | 10:01 AM | Joe | Parking Garage | 4 |
| 8/21/2021 | 11:00 AM | Sally | Peterson Wing | 3 |
I've been poking around with the RANKX and count functions, but I haven't been able to put together much. Any ideas?
Eventually my goal is to tie this in to a Synoptic Panel map, so that I can use a slider to see where traffic flow throughout the building goes throughout the day or based on specific user groups.
Thank you!
SamErenberger , I think you need a rank column like
rankx(filter(Table, [User_id] =earlier([User_id])), [date time],,asc,dense)
In case you do not have a date time column
create one
date time =[date] + [time]
3 Replies
SamErenberger , I think you need a rank column like
rankx(filter(Table, [User_id] =earlier([User_id])), [date time],,asc,dense)
In case you do not have a date time column
create one
date time =[date] + [time]
Thank you! That works great. This also gives me a great starting point of researching more into the RANKX function!
Adding a few more comments in case anyone is looking at this topic in the future (hi future people!).
In order to only show non-consecutive location changes, since my original dataset will sometimes push a 'location change' that's in the same place as the previous location, I created two new columns:
1. repeat_row, which concatenates User ID, Location Name and the hour that they're there. It's not perfect - if someone shows up twice consecutively but over seperate hours it won't show it repeating - but it's good enough for now.
repeat_row = [user_id] & [location_name] & hour(Traffic_Flow_Data[time])2. Then I created a 'repeat rank' column which shows how many times repeat_row shows up.
repeat_value = RANKX(FILTER(Traffic_Flow_Data, Traffic_Flow_Data[repeat_row]=EARLIER(Traffic_Flow_Data[repeat_row])), [event_timestamp], , asc, Dense)Then I created a new field that concatenates user ID and date, but only if the repeat rank = 1 - meaning it's not a repeated row.
user_date = IF([repeat_rank]="1", CONCATENATE(Traffic_Flow_Data[user_id], Traffic_Flow_Data[date]), "xxx")now, in my new column loc_rank, it will rank only the users that show up. User 'xxx' will get ranked in the hundreds but I have filtered that row out in all of my reports. This is using amitchandak fabulous answer from earlier.
loc_rank = RANKX(FILTER(Traffic_Flow_Data, Traffic_Flow_Data[user_date]=EARLIER(OnsiteUnnested10k[user_date])), [event_timestamp], , asc, Dense)There's probably a less convoluted way to do this, and if you have one I'm all ears! But it's working ok for now so it's my current workaround 🙂
