Traffic Flow - Rank and Counts

[SamErenberger]

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!

[amitchandak · Accepted solution]

@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]

[SamErenberger]

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 🙂 

[SamErenberger]

Thank you! That works great. This also gives me a great starting point of researching more into the RANKX function!