Formula to Summarize spending each week to display in a Dashboard

Ethan T

Hello,

I have a large sheet that we use for procurement for our company. Employees use a form to requisition items, and then when I place the order for them I input the date ordered into a column. Another column tracks the cost of each order. I would like to have a formula that uses the "Ordered Date" column to add up the cost of all the orders for each work week, and I'd like to display that number in a dashboard chart. So far, I have only been able to use the Summary tool in a report to group multiple orders placed on the same day into a total for that day, but the graph is too crowded this way. I have seen other posts detailing how to do this monthly, but we are on a weekly order structure. Ideally, I'd like to have the weeks grouped Thursday-Wednesday, but if it's easier then Monday-Friday would suffice.

I have attached screenshots of the relevant portion of the master sheet, with the relevant columns highlighted. I have also attached the graph in it's current form (if anyone knows how to flip the graph so it reads dates left to right instead of right to left please let me know), and my existing summary column with non-relevant information censored.

Any help would be greatly appreciated.

brianschmidt

First of all, it looks like your dashboard widget is sorting dates in the same order that your report is set up to sort. You should be able to just sort the dates in the sort tab on your source report.

As for sorting by week, I would suggest setting up a helper column named something like "Week of" with the following formula:

=IF(WEEKDAY([Ordered Date]@row) = 5, [Ordered Date]@row, IF(WEEKDAY([Ordered Date]@row) > 5, [Ordered Date]@row - WEEKDAY([Ordered Date]@row) + 5, [Ordered Date]@row - (WEEKDAY([Ordered Date]@row) + 2)))

This will treat every Thursday as the first day in a week. Then, you can sort your report based on this column, then also being able to summarize by the week cycles you're looking for.

Hope this helps!:)

brianschmidt

No worries! I was able to get the following to work predictably:

=IF(ISBLANK([Ordered Date]@row), "", IF(WEEKDAY([Ordered Date]@row) = 5, [Ordered Date]@row, IF(WEEKDAY([Ordered Date]@row) < 5, [Ordered Date]@row - WEEKDAY([Ordered Date]@row) + 5, [Ordered Date]@row - (WEEKDAY([Ordered Date]@row)) + 12)))

I also added the ISBLANK function so that you don't get the #INVALID DATA TYPE error when the ordered date column is blank.

Hope this helps!:)

brianschmidt

First of all, it looks like your dashboard widget is sorting dates in the same order that your report is set up to sort. You should be able to just sort the dates in the sort tab on your source report.

As for sorting by week, I would suggest setting up a helper column named something like "Week of" with the following formula:

=IF(WEEKDAY([Ordered Date]@row) = 5, [Ordered Date]@row, IF(WEEKDAY([Ordered Date]@row) > 5, [Ordered Date]@row - WEEKDAY([Ordered Date]@row) + 5, [Ordered Date]@row - (WEEKDAY([Ordered Date]@row) + 2)))

This will treat every Thursday as the first day in a week. Then, you can sort your report based on this column, then also being able to summarize by the week cycles you're looking for.

Hope this helps!:)

Ethan T

@brianschmidt You, sir, are my hero. I've been struggling with this for hours. Thank you!!

brianschmidt

https://community.smartsheet.com/discussion/comment/427088#Comment_427088

That's awesome! I know how it can be to struggle with something like this, and how satisfying it is when it works. Glad I could help:)

Ethan T

@brianschmidt I'm looking now to expand on the previous chart. Each purchase we make has a project name assigned to it. I would love to be able to turn the current Column chart into a Stacked Column chart, where we can visualize how much of each week's spending is coming from a given project.

This is what the chart looks like following your previous solution.

I have now imported the project column (with some confidential details censored obviously). Each item on the list has a project assigned for accounting purposes. See below:

However, when I try to change the format to stacked bar and include the new data, nothing happens. "Project" is also not listed as a possible column on the chart config (I have refreshed multiple times and made sure changes are saved on the report). See below:

I have also tried grouping by project as a second level group beneath the Week Of group. See below:

The report does a great job of grouping the data in this way, but I'd love to be able to present this data visually to our company president so he can see where the spending is happening.

What am I doing wrong? How would I get the chart to display the amount spent each week as a makeup of project costs on the bar graph?

Thanks again for your help

-Ethan

brianschmidt

Unfortunately, you've run into something that is a major frustration for many Smartsheet users...Reports on dashboards can only pull from the first level of summary data, meaning that you can create a report that shows total costs by report or total costs by week. You could create a helper column which combines "Week of" and "Project" and then sort/summarize your report that way. However, then you end up with every project's week represented as a separate bar rather than being able to take advantage of supposed stacked column graphing.

Several people have gone back and forth from what I seen finding solutions for this, but I haven't seen one that is very seamless. I've linked a couple of discussion posts about this below. You can also submit this as a product improvement idea: https://community.smartsheet.com/post/idea/smartsheet-product-feedback-%26-ideas . The more common and upvoted ideas, the more likely they are to be integrated into changes Smartsheet makes.

Sorry I couldn't provide more guidance on your follow-up question!

Past Discussion Resources:

https://community.smartsheet.com/discussion/84226/create-stacked-bar-chart-from-multiple-columns-of-a-summary-report

https://community.smartsheet.com/discussion/81983/new-create-a-chart-from-reports-with-grouping-and-summary#latest

https://community.smartsheet.com/discussion/96622/pie-chart-summarized-columns-in-a-sheet-summary-report

Ethan T

Hi Bryan,

Thanks for looking into this for me. I had seen several forum posts asking for something similar as well but was yet to find a solution. Definitely frustrating that this isn't integrated yet considering some of these posts are years old. Unfortunately, the workaround in the first post by Baptiste.J I think is beyond my abilities to figure out. Regardless, thanks again for having a crack at it. If anyone else stumbles onto this thread that can help walk me through applying this workaround to my situation, I would very much appreciate it.

Ethan T

@brianschmidt I've noticed as time goes on that the week of formula you sent me attributes items to the previous Thursday (See below), however what I'd really like is for the formula to display the Thursday at the end of the week, if that makes sense. Almost like a "week leading up to", if that explains it better.

As you can see from this photo, orders placed on April 23rd are getting placed into the week of April 18th (last Thursday). What I'd like is for it to show is the week of April 25th (this Thursday). That way I can demonstrate how much we spend since the last "week of" instance each Thursday at our financials review. I'm sure this is a minor tweak to your formula but to be honest I'm scared to make a change and break it since this sheet has grown so large.

Can you advise how to correct the formula to get it displaying the way I mentioned?

Thanks in advance.

-Ethan

brianschmidt

No worries! I was able to get the following to work predictably:

=IF(ISBLANK([Ordered Date]@row), "", IF(WEEKDAY([Ordered Date]@row) = 5, [Ordered Date]@row, IF(WEEKDAY([Ordered Date]@row) < 5, [Ordered Date]@row - WEEKDAY([Ordered Date]@row) + 5, [Ordered Date]@row - (WEEKDAY([Ordered Date]@row)) + 12)))

I also added the ISBLANK function so that you don't get the #INVALID DATA TYPE error when the ordered date column is blank.

Hope this helps!:)

Ethan T

Looks like you've done it again! Thanks a bunch Brian. And the ISBLANK function has been an added cherry on top.

brianschmidt

Awesome! Happy to help