High Passenger Counts per Date and Station

[macedofilhoru]

Goal: Produce a report detailing the stations by date and route with the highest number of CCJPA passengers on board (see attached mockup excel file).

Datasets Used: Payments - CCJPA/mart_payments/Fct Payments Rides V2 Payments - CCJPA/mart_payments/uploaded_data/Payments Ccjpa Stations And Sequences

Problem: Several operations within Metabase, which should be simple, are not functioning properly, especially the joins.

Suggested Steps: 1. Define the Sequence Number of Each Station by Direction (westbound and eastbound):

• Left join “Fct Payments Rides V2” with “Payments Ccjpa Stations And Sequences.”
• The result should be a table with transactions and numbered stations based on the direction.

2. Count Passengers Onboarding and Offboarding:

• For each date, route, and direction, count the number of passengers onboarding and offboarding per location. -This step should result in two tables: one for onboarding passengers and one for offboarding passengers.

3. Combine Onboarding and Offboarding Data:

• Perform a full join of the two tables from the previous step by date, route, and station (matching onboarding and offboarding stations).
• Subtract the number of offboarding passengers from the number of onboarding passengers to get the net number of passengers per station, route, and date.

4. Calculate Cumulative Sum and Identify High Count Station:

• Arrange the results by station sequence number (from Step 1).
• Perform a cumulative sum of the net number of passengers per day and route.

• CCJPA High Counts_mockup.xlsx Identify the station with the maximum number of passengers per date and route.

[akosmatzon]

Hi @charlie-costanzo , this is the CCJPA calculation we mentioned last week. This is part of the Amtrak reporting (CCJPA reporting towards Amtrak), it will be sent via email on a daily basis. It is important and urgent but we couldn't solve it in Metabase. We appreciate your help!

[erikamov]

I had a meeting with Akos and Francisco to talk about this ticket and we found some solutions on how we could map the direction of a route. The final results were sent to them for review. Once it is approved we can add to the dashboard.

[erikamov]

The report was approved by @akosmatzon and created on CCJPA Dashboard.