Help with Trend Data

Rick Floyd

I need some help with collecting data for trending.  Currently, I have multiple projects with all of their top line data dynamically linked to a Roll-up Sheet.  On this roll-up sheet I have data that is calculated from the project Status.  Ex: How many projects are Red, Yellow and Green.  This works really well for live data.  

But, management is wanting to collect this data over time.  Example: On the first of each month, they want to know the number of projects that are Red, Yellow, Green and create a dashboard showing how the projects are trending from month to month.

I'd like to know how to automatically capture this data on the triggered date (1st of each month).  Once I have the data, I could then create a dashboard.  I could do this on the current Roll-up sheet or create a separate trend collection sheet if needed.

Please let me know if you have some direction and help.

Rick

 

Paul Newcome

You would need to establish the dates of when each status changes. As of right now, this would have to be manually done unless you have access to a third party tool such as Zappier or the Smartsheet API.

Andrée Starå

Hi Rick,

I'd recommend setting something up with the help of a third-party solution like Zapier or the Smartsheet API.

Would that work? Can you share some screenshots? Might have some other ideas.

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Rick Floyd

So here are some screen captures to help with my issue...

The file "Capture Roll-up.png" (first file shown) is a screen capture from my roll-up sheet that takes the project list, and calculates with COUNT and COUNTIF formulas, the TOTAL number of Projects and the STATUS COUNT of each project metric with the number of R,Y,G projects for those metrics.  This works perfectly for real time data.  The projects get updated and the status changes instantly for each of the KPI's that are calculated on a project by project basis.

What I'm looking for is an automated way to collect the data on the first of each month and store that snapshot.  Looking at the second file "Data Specific Data Capture" (second file shown), you will see it is a series of data matching the realtime data, but captured on the specific dates.  Right now, on the first of the month, I send myself an automated reminder to go capture the data manually.  I'm looking for a more elegant, automated solution to grab the data from the first row and subsequently populate the area below it with the data from those predefined dates.  This data then feeds my trend dashboard.

Right now, if I act when I get the reminder, it's a simple copy and paste the data.  There must be a more elegant way to do this....

Thanks for any additional help you can provide.

Rick

 

Rick Floyd

We want to collect the data on the first of each month.  I'm doing this manually right now.  But, there MUST be an easier way to tell SmartSheet on June 1st, collect the data that is in row 1 and copy it to row xxx that would correspond to June 1st, 2019.  Then do that again on July 1st for the next row, then August 1st, the next row and so on....

Paul Newcome

As of right now... There is no way within Smartsheet to lock in a time stamp for a specific action automatically. It has to be done manually. You would have to manually enter the dates when something changes to be able to track it without going outside of Smartsheet.

Rick Floyd

Thanks to everyone for their suggestions.  The solution turned out to be easy.  Here is what I did:

1 - Created a Sheet titled "Management KPI Roll-up Sheet"

2 - In the new sheet, I used the same columns as I did in the "Global Project Roll-up Sheet" possessing the data I want to retrieve.

3 - In the new sheet, I also added a Date column called "Data Collection Date" to identify by row, the date the data is to be collected.  I populated this column with the monthly dates (07/01/2019, 08/01/2019, etc. in rows 1,2,3... )

4 - Then, I used the formula:  =IF(TODAY() = [Data Collection Date]1, {Global Project Roll-Up Sheet Range 1}) and identified the cell I wanted the data pulled from.  That looks at today's date and pulls the data from the linked cells/sheet.

5 - Finally, I created an Automated Action that will lock the row when the data is retrieved, and automatically send an email verifying the data was collected to the team member(s) whom the sheet was shared.

It sounds complicated.  But, it is really simple and works well so far.  I just need to verify that it will collect the data if the Sheet is closed...  I'll report back after further testing.

Paul Newcome

You will need to open the sheet for it to collect the data because of the TODAY function.

 

Also... How will this track historical data to show the trend?

GB

That nearly works for me.

I've got everything working on a single sheet, with the top row containing the links to the live data, and rows 2 onwards containing the capture, triggered on date, then locked when it is updated.

The issue I've also found is that unless I open the sheet on the 1st of the month, it won't update, so even if I set a reminder to manually open the sheet, if that day falls for a non-working day and I open the sheet on the 3rd of the month, the TODAY condition will not be met, so the data copy won't work.

Any ideas?

Paul Newcome

You can accomplish this with the free version of Zapier. You can set a trigger to add a new row to a sheet on a regularly scheduled basis. Monthly is an option. I use daily (which is still able to work on the free version) because I need to pull daily metrics, but that's entirely up to your preference.

 

I suggest creating a completely new sheet. Turn one of the columns into a date column. Add the 

 

=TODAY()

 

function.

 

Then set Zapier to add a new row to your TODAY sheet. I labelled mine "zzzTODAYzzz" just to keep it all the way at the bottom of every list and out of the way and make it easier to find when searching through all of my sheets while setting up the Zap.

 

Use a cell link on your metrics sheet to pull the date from your TODAY sheet, and you will have a regularly updating TODAY function. I link all of my metrics sheets to that cell then use a cell reference in my formulas to point towards the linked cell.

 

It works wonderfully, only requires maintenance when you get to the maximum 5000 rows, and is actually pretty simple to setup and use.

Hasan Syed

@Paul Newcome

I am attempting to set this up however I cant seem to find a trigger on an automated cadence. Has this option been phased out between smartsheet and zappier?

The only options I'm seeing are Updated Row, New attachment, new row, new comment.

Does this mean I have to open the original page every Friday(weekly desired cadence)?

Andrée Starå

Hi @Hasan Syed

I hope you're well and safe!

You actually don't need the Zapier solution anymore because Smartsheet released a feature called Record a date.

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Hasan Syed

Thanks @Andrée Starå great insight.

Andrée Starå

@Hasan Syed

Thanks!

You're more than welcome!

Pam Ferguson

One way around to get the "Today" function to work on your sheets is to automatically lock a row (at 1:00 AM each day) and then unlock the row (at 2:00 AM each day). This should help automatically trigger that workflow for you.

Nora G

https://community.smartsheet.com/discussion/comment/172016#Comment_172016

Hi Rick - I have created a sheet doing the same and it works beautifully on the day that is in the Date column. However, when I open the sheet the next day, the data has disappeared! I've tried an automation to lock the row, however, the data still seems to disappear since today() is no longer equal to the date in the Date helper column. Any suggestions?