How do I create a dashboard to show orders shipped?
I would like to create a visual dashboard to show a graph of OTD, #/% Orders shipped/complete by customer, #/% orders shipped YTD by product, Orders open vs. shipped YTD, and orders shipped by month. I don't know why I am having a hard time with this and it seems a lot of the templates are to forecast future sales/orders and present sales ($) to date. I am pretty good at creating sheets and filtering data for reports. I am lost when it comes to gathering data and presenting on a dashboard.
Answers
-
Hi Jennifer,
You're right in noting that this data would need to be gathered first, before it can be put on a dashboard. I would suggest creating a new sheet specifically for this purpose... either a Metrics sheet or a Rollup Data sheet you can use for any of these calculations to eventually have displayed on dashboard.
Then you could use cross-sheet COUNTIFS formulas to calculate numbers based on criteria. If you're having trouble creating these formulas, the Community is a great place to ask for formula creation help, but we do need a bit more information. Could you post screen captures of an example sheet specifying the criteria you want to count?
After your formulas are created and you've got your calculations, we have a "Preparing Dashboards" webinar that you may be interested in (see here), or, based on the account associated with your email address, you have the ability to book Pro Desk sessions (see here). These are 1 on 1 coaching sessions over a 30-minute screen share where you can discuss best practices or how to get from step A to step B.
Let me know if you have any questions on this!
Cheers,
Genevieve
-
Thank you for your response! It's 4 years later and took your advice - populated the data (received order date, customer, order #, confirmed ship date, customer requested ship date, actual ship date, material, person responsible (to fill the order), etc! I am hoping 4 years is enough populated data to now make this into a visual dashboard for others to view and possibly become interactive (click on a customer name to populate a list of orders to said customer; same with material; etc). If anyone has any advice, 4 years later on how to do this, please let me know!! Note: i do not have predecessors and/or calculated days past/future (if that makes sense)! I am including a screenshot of my sheet headings, for reference to see if there is anything I need to add and where to go from here :)
-
Hey @Jennifer Kohl
My apologies for the delay! It's nice to hear from you again 🙂
I'm certain you have enough data to create a Dashboard, but what that looks like will depend on exactly what you want to show.
One quick and easy way to build charts is to use a Report to filter your rows based on a certain criteria (such as timeframe), or show all rows, then use one level of Grouping and then Summary to create header rows with numbers/data that a chart can surface. Reports update as the underlying sheet updates so your charts will stay relevant as new data comes in. Here's a webinar you may find helpful: Redesigned Reports with Grouping and Summary Functions
However there isn't an automatic way to make your Dashboard "dynamic". You can configure your widgets to open certain things when they're clicked on (such as an Image widget or a Chart widget) but you'll need to create a sheet or report that has the filtered data you want to display (or a sheet URL with the filter included) for that link to surface the relevant information.
Cheers,
Genevieve
