Data Over Time Report/Dashboard

Ashley B.

Hello,

What steps should I take in creating a dashboard that includes a graph displaying data over time? I would love to be able to click on each data point and a pop up of that data point displays information during that time period.

Here is an example of something I would like to create in Smartsheets

Paul Newcome

You would need to create a table where the dates you want to plot against are in one column and then your numbers you want to actually plot are in another column.

Ashley B.

Thank you for your response Paul. I guess I was thinking more along the lines of linking a report and the graph automatically showing the fluctuations within a time period, like within a month's time.

Paul Newcome

You could create a report, but the data would still have to be the same structure. Do you have a screenshot you can share of the source data?

Ashley B.

Hi Paul,

Here is a screenshot of the data source

dylanchaney

You have to use a combination of helper columns and the MID function on a separate sheet. I named my separate sheet "CIM's Over Time".

I have my columns set up to capture the following info for Construction Impact Messages (CIM):

• [Column Name], description, formula
• [Today's Date], date of CIM submission, data from my form
• [Months], month of CIM submission (in number format), using =MONTH([Today's Date]1) and copied to all relevant rows
• [CIM Month], 3 digit month as "MMM", using =MID("JanFebMarAprMayJunJulAugSepOctNovDec", ([Months]1 * 3) - 2, 3)

I made my first column the month format I want to display on my chart and then made my second column a count of all the same months in my [CIM Month] column and matched it against my [Month Abbrv.] column using =COUNTIF([CIM Month]:[CIM Month], [Month Abbrv.]1).

This gave me a count of how many CIMs I had during a given month and gave me my parseable data chart.