Weekly Data Report Showing Trends by Month

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!


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 08/04/23

    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
    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.
    Genevieve P. Employee Admin

    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
    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
    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.
    Genevieve P. Employee Admin

    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
    MDexter ✭✭✭
    edited 08/07/23

    That worked! Thank you so much!

  • Genevieve P.
    Genevieve P. Employee Admin

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!