Weekly Data Report Showing Trends by Month

MDexter

We export data weekly showing the number of registrations for a variety of products. I have the data in a line graph showing the trend over time but the graph doesn't display the value of the date. Ideally, I would like to simply display the value of each month on my dashboard while retaining the data that is updated weekly.

Is there a formula I can use to display the data monthly or even compare month over month data trends based on the way my sheet is set up? Any assistance is appreciated!

Genevieve P.

Hi @MDexter

What I would personally do here is set up a Helper Column next to your Date column that pulls the MONTH from your Date value and returns it as text. For example, something like this:

=IF(Date@row <> "", 
IF(MONTH(Date@row) = 1, "01 January", 
IF(MONTH(Date@row) = 2, "02 February", 
IF(MONTH(Date@row) = 3, "03 March", 
IF(MONTH(Date@row) = 4, "04 April", 
IF(MONTH(Date@row) = 5, "05 May", 
IF(MONTH(Date@row) = 6, "06 June", 
IF(MONTH(Date@row) = 7, "07 July", 
IF(MONTH(Date@row) = 8, "08 August", 
IF(MONTH(Date@row) = 9, "09 September", 
IF(MONTH(Date@row) = 10, "10 October", 
IF(MONTH(Date@row) = 11, "11 November", 
IF(MONTH(Date@row) = 12, "12 December"

Then once you have the text displayed, you can create a Row Report and Group it by the Helper Month Column.

This will bring up a header row with the Month name. In that header section, you can also then add a Summary, such as the SUM of your other values or AVG.

The first level of a Grouping and Summary in a Report can be used as a source for a Chart on a Dashboard! Here's a free webinar with more information: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

MDexter

Thanks so much, Genevieve! I followed your instructions, built the Row Report, and Grouped accordingly. When adding into the chart widget on the dashboard, I'm not getting the horizontal row to display the month associated with the data. I'll see if the webinar link you sent has more information on creating the widget using specific months or better delineating the data elements on the dashboard. I really appreciate your help as I am so much closer to where I need to be!

Genevieve P.

Hi @MDexter

Can you post a screen capture of how you have your Chart set up?

Here's my example Report:

Here's a Chart I generated from this:

MDexter

Here is a copy of how I have the report set up. I was trying to make a line graph but a bar graph would work fine as well. When I go to add it to the dashboard, I can't label each column as the month is corresponds to.

Thank you again for all your help with this and your quick response!

MDexter

I did just realize I had a filter on there. I removed the filter but that didn't effect anything on the widget.

Genevieve P.

Hi @MDexter

I can see that in the "Columns Included" section you only have one column selected. Make sure you select the Primary column as well, where the Grouping name shows up. Then ensure the tab for "Use first column as series labels" is toggled on.

Let me know if that now works for you!

Cheers,

Genevieve

MDexter

That worked! Thank you so much!

Genevieve P.

Hi @MDexter

Yes exactly! You've almost got it. You're right, it pulls from the Summary row, which displays the value in the Primary column 🙂

Change the "Data Pull Column" to be the "Primary" column. That should be where the Month data is stored in the Summary row of the Report.

Hannah_Trousdale45

Hi @Genevieve P. I am struggling to figure out the IF statement to group these dates by month. All I need is July, August, September right now. These dates are being pulled from another sheet which I will include below. I am just not sure how to make it look like 07 JULY (for example). Please let me know what I should be inserting in my IF statement, thank you.

Genevieve P.

Hi @Hannah_Trousdale45

As long as your column titled "Form Date Field" is a date type of column, then you can reference that cell in your MONTH function instead of using DATE(yyyy,mm,dd)!

Try:

=IF(MONTH([Form Date Field]@row) = 7, "07 - July", IF(MONTH([Form Date Field]@row) = 8, "08 - August", IF(MONTH([Form Date Field]@row) = 9, "09 - September")))

And so on 🙂

Hannah_Trousdale45

@Genevieve P. Great, that worked! Do you know how I can get the averages for each month? I wasn't sure if I needed all 12 months to have data in order to make that appear. Here is what my screen shows when I try to average them:

Hannah_Trousdale45

@Genevieve P. I also would like to know how to make "09 - September" auto populate once a new ticket is submitted. Otherwise, I will have to fill in the formula each time a ticket gets submitted. See image below.

Genevieve P.

Hi @Hannah_Trousdale45

The Report will only be able to average data if all the data is numerical - meaning that the content of each cell is a number (appearing on the right-side of the cell). Is it possible that the content is being seen as text, instead of numerical?

In regards to the formula, after you've put it in one cell you can right-click on the cell and apply this as a Column Formula! 🙂

Here's more information: https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Cheers,
Genevieve