I'm trying to figure out how to set-up Data Shuttle to show changes from the last time a workflow was run. For example, I have a sheet with the following columns. "Job Title", "Job Code", "Posting Number" (Posting Number is the unique identifier) and "All Submitted Applications". "All Submitted Applications" is the number of applications received that day. A report is run each day to provide the updated number of applications received. I need an automatic way of seeing which postings have received new applications one day to the next. In other words, we need it to be a rolling comparison to highlight each time the number of applications increases from the day before.
I have set it up so that my source sheet has all my columns plus, a column titled, "New Applications" where I have a formula to calculate the difference from "Today's # of Applications" and "All Submitted Applications" The Data Shuttle maps the "All Submitted Applications" column from the Excel report to the "Submitted Applications Current" column in the smartsheet. I then have a column formula in "New Applications" column that shows the difference between the two columns and conditional formatting to highlight those rows where "New Applications" is greater than 0. It works great, except that I need it to calculate the number from the previous day rather than from the original data, and if I had it replace the "All Submitted Applications" during the data shuttle workflow, I wouldn't have the original number to calculate the difference. I also need it to add new postings as they are created and delete postings as they are filled.
Below are a couple screenshots and attached are two sample Excel Report with dummy data for Day 1 and Day 2. I didn't know if there was a way to use Input Expressions to assist with this or if there is a better way to handle altogether. Thank you for any assistance!!
It wouldn't be through data shuttle. You would need to set up a separate metrics sheet with cross sheet references in formulas such as COUNTIFS to get your counts. Then you would set up a copy row automation to a third sheet to run daily (either right before or right after the data shuttle runs whichever you prefer).
Paul Newcome ✭✭✭✭✭✭
