Questions:
I am looking to making some dashboards. I have a question about the Data Table layout.
We are tracking 6 KPIs, three times each day at 3 locations.
Depending on how the data is stored we could have 3 rows or 17 rows of data.
We will keep collecting data over time and expect to end up with at least 2 years of data.
Question #1
Look at the enclosed Excel data file. It contains 2 data tables.
Which would be the best to use for performance?
Which would be the best to use for ease in creating the underlying Pivot tables and Charts?
Question #2
I have dates going down the left hand side of the table.
I also need, Year, Month, Week and Day.
It seems to me like I should only need to store the date once and not Date, Year, Month and Day.
Your Thoughts,
Richard
Hi Richard,
I would use a third layout that is similar to your second table, except the KPIs are split into their own columns. i.e. you'd have the following columns:
Date County Status SH* RH* Min/Trip Miles Hours Trips
*I'm not sure what SH and RH represent, so maybe these should be in another column like the Status column?
You should also have a date/calendar table that has the following columns:
Date
Year
Month
Week
Day
Hope that helps.
Mynda
Mynda,
- I understand what you’re saying about the date table: I will create one.
- Not quite clear about the third data table layout.
- The SH is Scheduled Hours, and the RH is Revenue hours. We use these KPIs because this is a transportation system AKA a Bus company. And these are Paratransit trips.
- We are want to compare SH, RH,Min/Trip/Miles,Hours and Trips byDate, Year, Month, Week, Day, Location, and status.
Status Being:
SCH = Trips scheduled.
OPP = Trips Optimized.
LD = Live Day, how were trips performed.
SH = Schedules, RH = revenue Hours, Min/Trip is Minutes per trip, Miles is Miles traveled, Hours is Hours the driver gets paid for, and Trips 15 the passenger count.
- Seems line you’re suggesting a one column table, is this correct???
Thanks
Richard
"Seems line you’re suggesting a one column table, is this correct???" No, not at all. There are 9 columns as listed above, and again below:
Date County Status SH RH Min/Trip Miles Hours
Trips
I just didn't know what the acronyms SH and RH represented, so didn't want to assume they should be separate columns. From what you explained above, they should be separate.
Mynda
