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.