Creating Admin Dash

I have a sheet of data tracking distinct pickups: # of gallons picked up, special services, comments, etc. I want to create an Admin dashboard with Big Number, but I can’t seem to figure out how to use it. I believe I need a sheet with rollups of the major metrics I want to use in Big Number, but how would I structure it?

I also want to have total gallons picked up for each day over the past 5 days…but again not sure how I would structure the data.

Also, can I add custom time range that would adjust all the data within my Admin Dashboard tab?

Is there a way to add an “Export” button so that I can export in csv or excel?

Any other good admin features to roll up insights?

In short, you would have to use a combination of relations/queries + rollups to calculate the numbers, in the table you want to build the screen on. I suggest building it on the User Profiles table, and filter the screen to the signed-in user’s row.

  • Create a helper table that would contain 5 rows.
  • Add an “index” number column that would contains the number 0, 1, 2, 3, 4
  • Add a math column to calculate the date, the formula would be Now - “index”. Format it as “date only”.
  • Add a math column to calculate the numerical value of the date. The formula would be: YEAR(D)*10^4 + MONTH(D)*10^2 + DAY(D) with D being the value of the previous math column. You would get something like 20230721.
  • In your Pickups table, add a math column with the same formula as above. The D now would take the value of the recorded date.
  • Create a multiple relation from the Helper table to the Pickups table using the two columns above.
  • Create a rollup to sum the amount of gallons for each date, and display the Helper table as a collection.

Mostly the same as my first answer, extra things to do are:

  • Add 2 user-specific columns to hold the “Start Date” and “End Date” column in your user profiles table.

  • In the Pickups table, add an if-then-else column. If “Record Date” is before “Start Date” then return nothing, if “Record Date” is after “End Date” then return nothing, else return a text like “true” or “Filtered”.

  • In your User Profiles table, create a query pointing to the Pickups table and filter by the if-then-else column being not empty.

  • Work your rollups based on the query, and display the numbers in the Big Number component.

2 Likes