How can I chart the last 7 days that contain data?

Hello, I am capturing audit data utilizing the web form for smartsheet, and would like to use a line chart to show average compliance per day, for the last 7 working days. With the holiday weekend that just occurred, I have a 3 day hole in my chart.

I have been brainstorming and am trying to come up with a formula that will first check if there is data on the current date, then evaluate the previous date for data, and so on until there are 7 days with data.

I am fairly new to Smartsheet and am brushing off the function cobwebs, so any help would be appreciated.

Thanks,

-Jim

Find more posts tagged with

Formulas

Dashboards

Accepted answers

Paul Newcome

Try adjusting the formula in the DateHelper column so that it is a WORKDAY function.

All comments

Paul Newcome

Are you able to provide a screenshot of the source data that you are using to create the graph?

JBalow

Hi Paul,

Here is the source data, DIVIDEBYZERO are dates where an audit has yet to be completed.

DateHelper is a simple TODAY() - X

Date is just converting the DateHelper into text so it can be charted

Line 1, Line 2 is showing the average compliance of the audits for each line by day

Here is the actual sheet the form data is loading into.

Please let me know if you need more information Paul,

-Jim

Paul Newcome

Try adjusting the formula in the DateHelper column so that it is a WORKDAY function.

JBalow

Ah, like I said, function cobwebs! Thanks Paul.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.