Weekly Average Graphed by Month

ABauman
ABauman Member
in Beast Mode

I am looking to graph sales data of the weekly average of items sold per month, not sure of the best way to accomplish this. The current dataset that I am working with is essential a transaction detail dataset coming out of NetSuite Analytics Connect, my query references several different tables but I have joined them all together.

I have attached an image of the graph that was generated from an excel sheet that we are looking to recreate.

image.png

Tagged:

Best Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓

    Keep in mind this is not a fully fleshed out solution, but does work if you are just looking at general trends. Depends how exact you want/need to be in your weekly average. But one possible solution is to graph by month, and then have a beast mode like this.

    SUM(value)*12/52

    This is likely better than just dividing by 4.

    You could also adjust this as needed to dial in how you want to approach weekly average.

    SUM(value)12/365*7

    Then just set it up in a stacked bar chart with your group as the series and weekly_average as the y-axis. It's important to note that this approach isn't super flexible. You'll need to always graph by month, or adjust your beast mode. If you wanted a more robust option, that is possible, but would use a date dimension table and ETL.

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Here's what I came up with on how to solve this:

    https://youtu.be/EnN4zuXVkRI

    If I solved your problem, please select "yes" above

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 2024 Answer ✓

    Keep in mind this is not a fully fleshed out solution, but does work if you are just looking at general trends. Depends how exact you want/need to be in your weekly average. But one possible solution is to graph by month, and then have a beast mode like this.

    SUM(value)*12/52

    This is likely better than just dividing by 4.

    You could also adjust this as needed to dial in how you want to approach weekly average.

    SUM(value)12/365*7

    Then just set it up in a stacked bar chart with your group as the series and weekly_average as the y-axis. It's important to note that this approach isn't super flexible. You'll need to always graph by month, or adjust your beast mode. If you wanted a more robust option, that is possible, but would use a date dimension table and ETL.

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Sean_Tully
    Sean_Tully Contributor

    I don't have a solution at the moment, but I think the solution will depend on how your data is structured, if there are records for every date, and how you want to handle when a week straddles two different months.

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Here's what I came up with on how to solve this:

    https://youtu.be/EnN4zuXVkRI

    If I solved your problem, please select "yes" above

  • ABauman
    ABauman Member

    @ColemenWilson Thanks for your help on this! This is exactly what I needed.

Categories