Can I Report Out Using a Comparison of Previous and Current Values Using a Threshold

I have a Sheet used for tracking new business opportunities. We have this line we draw in the sand. When an opportunity reaches a Confidence Level of 75% and some other criteria happens, steps are required to be taken.

"Confidence Level" is a dropdown column where folks select an option of either 25%, 50%, 75%, 90%, 100%, or 0%.

There is an ask for a Report to be sent to a distribution list every Monday to include any opportunities that have either:
a.) Increased from below 75% to at or above 75%
b.) Decreased from at or above 75% to below 75%

The request is to show the line items with their previous and current Confidence Levels, so the email recipients can see at-a-glance which line items had this type of change which means their attention is required.

If only we could use Automations to apply or execute a formula, or even copy an entire column worth of values to another column (on a set schedule, and not via a column formula). Each way I think of is restricted in some way and not possible.

The only thing I can think of that may work is creating another Sheet that is fed by the tracker on a weekly basis, and then potentially analyzing this Sheet for changes between the last 2 dates worth of entries. Even with this, I wouldn't know how to get the line items into a Report because values for each opportunity would be stored on different lines. Then that may require yet a third Sheet and then a Report. I just don't know…

Any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

Ray Lindstrom

Advanced Work Management. Interesting, haven't heard that thrown around before. I'll have to try to remember that.

I appreciate the input. Unfortunately that won't do what I need. I can write a formula to pull the data for one item at a time. The end result I'm looking for is a report listing all items meeting my criteria.

I turned to Power BI instead. There I used 2 data flows to capture the data on a carefully timed schedule. Those feeding into a single semantic model gave me the data I needed. I was then able to create a Report with a table of items meeting my criterion. After proving that worked, I turned to Power BI's Report Builder app where I built the final report that publishes to the same Power BI workspace as the other files. I configured a schedule to email a pdf of the report to a distribution list, so the users could have the information in their inbox every Monday morning to help them quickly know what needed prompt attention that week. They can also use the URL for the report I built directly in Power BI to view the changes any day of the week for changes on the fly.

All comments

prime_nathaniel

@Ray Lindstrom this is better accomplished with bridge as that can trigger off a cell update and store the original and new value while it processes your logic. That said, if you wanted to do this NO prem apps / AWM you'd need to copy row everytime your cell changed. That would get you a log of every change made. You can then max(collect()) on time changed for your records unique ID where value does not equal the current value in the cell. Now you have the previous value.

Ray Lindstrom

Hello @prime_nathaniel,

Thanks for your time and input!

Not sure how that approach would get me to a Report. Can you elaborate? Also, what does AWM stand for?

BRgds,
-Ray

prime_nathaniel

@Ray Lindstrom AWM is advanced work management, you will hear about it from your rep at your next renewal. It is basically advance for the new USM subscriptions. The idea for the change log would be everytime the confidence value changes, it copies a log of the entry over to a "history" sheet. You then do a collection of the history sheet and just make sure to filter out your current row giving you all items BEFORE that update. The max of that collection is the "last update" before current.

it is not the most elegant workflow for what is a pretty simple trigger in bridge.

Ray Lindstrom

Advanced Work Management. Interesting, haven't heard that thrown around before. I'll have to try to remember that.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.