Daily Chart for a count on a Dashboard

Andrea Westrich

I want to create a dashboard chart with a daily count of the number of appointments set by day. How do I create that so the chart data is automatically updated?

Details: I collect data each day of every appointment set with customers with several columns of details. One column is Date Appt Set. I want to create a bar graph that is a count of how many appointments were set that day. All I need on my chart is the actual date and the count of the appointments set for that date. I don't want to have to reset the data set each day, I want it to be automatically updated without interference.

Let me know what other information I can provide to help with this.

Thanks

Malaina Hudson

Hi, @Andrea Westrich,

I would suggest an additional sheet as a summary and have the Date Appointment Set as a column (be sure it's the same format as your main data sheet), and a second column to put in a formula for counting the appointments. You can use the Primary column for your formula, but not your date.

Then, in the column containing your formula, use a COUNTIF function that counts the number of times the Date Appointment Set matches the target date.

Then, in your dashboard, you'll chart the summary sheet which contains the two critical elements of your chart.

Not sure how versed you on the COUNTIF, but give it a try and if you need more help, just @ me.

Andrea Westrich

Thank you @Malaina Hudson , I will try this today. But since this data is input and reported on every day, how do I get it to update automatically every day so I don't have to manually?

Andrea Westrich

@Malaina Hudson I was able to create my chart using a summary sheet and COUNTIF. Thank you! However, I still have to go in every day and update the data selection to include the new day's information. Do you know how to have the data and graph update automatically each day? Thanks

Andrée Starå

Hi @Andrea Westrich

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Malaina Hudson

Hi, @Andrea Westrich ,

I'm not sure what you mean by updating the range daily to add the new dates. Are you saying that you created your COUNTIF formula to say Date1:Date30 (assuming 30 rows in the range)? Or did you just use Date:Date to select the entire column? If you do not specify the rows and only identify the entire column, then you can add rows without redefining the criteria daily.

If, instead, you really mean that you must update existing dates in the range, then I'd try @Andrée Starå's solution to facilitate automated updating of existing data.

Andrée Starå

@Malaina Hudson

I reread it, and I think you're correct, and my suggestion isn't the solution.

@Andrea Westrich

I'd recommend adding the dates as needed and then use a report to not show the dates that aren't relevant.

Make sense?

Would that work/help?

Andrea Westrich

Hi @Andrée Starå and @Malaina Hudson,

My apologies for not being more clear. I do go in and enter the data every day (but I can't wait to try Andree's update process), the problem is that the data I input daily is used to make a bar graph, and each day I have to go into that graph via the Dashboard and manually select the new day (and reselect the columns) so the graph updates with today's data. How can I make sure my graphs update with each new day's entry?

Does that make more sense?

Andrée Starå

@Andrea Westrich

No worries!

I'd recommend using a report instead because it will be dynamic. The report would be updated automatically.

Make sense?

Malaina Hudson

Hi, @Andrea Westrich ,

I think @Andrée Starå might be onto something with the report, but if that doesn't suit, you could also use this method:

• Create a new sheet for a calendar summary

• Input your dates for the year (or your expected graphing cycle) into any column that is NOT the Primary

• In any other column, use a COUNTIF to count the number of times the Appointment Date Set appears in its column

• In your dashboard set the chart to chart the entire summary of dates and values

This will display a chart for the fully defined date period - meaning if you defined a year, it will display the whole year on the scale, and chart zero values for those dates that are not yet consumed with appointments. If that's more empty space than you want to see on your chart, then this method would allow you to define a different graph scale based on the selected date range and therefore change the frequency you'd need to update your graphing range. E.g., you could define a 15 day window to graph and allow the white space of future zeroes, then only update your graphic scale 2X per month. Or you could define a 90 day chart into the future, and update your graphic scale quarterly. This would be driven by your tolerance for white space, general dashboard aesthetic, and internal expectations of visibility; possibly also by any limiters or requirements for booking windows - but it would get you out of the daily graph reset cycle. Here is an example: