This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Attached workflow should get you there. Might be a simpler way to go about it, but it's been a long week and it's late.
(Note, I copied values from the image, so any inaccuracies in example are because I can't type. In the future attach a sample data set if you can. Always appreciated. I also did not include unnecessary fields.)
I've annotated the workflow for reference. In short:
Create a record ID based on address
Get a list of unique record IDs (since that list will change over time)
Filter out data by date after some formatting
Create a template sheet that's every date for every record ID, regardless of what's in the data
Join the actual data to that template sheet. There will be missing records
After figuring out prior year book value, join to template sheet and do a bit of formula work to fill in where necessary.
First off, thank you very much for your help. This workflow is driving me crazy.
I have tried to use this solution with the entire dataset, which is included in the workflow attached below and do have a few follow up questions, if you don't mind.
Please Note, as I am trying to create this workflow for minimal human interaction, I added a few formulas is determine whether or not the date is for Current Year or Prior Year.
1) For some reason, recordID 5 in this instance is not coming out of the joins down the workflow, and I believe it is caused by only have prior year dates and not current year.
I attempted to Union it with the rest of the dataset, but the details are missing.
2) For the final formula tool in your workflow, I see how it is layering on the book value by month and not changing the value until a new value is introduced, however, I need for it to take the sum of the values once a new one is introduced. For example, in the image below, we have $13,469 for January and February, which is perfect, but once March's value is introduced, march would show $14,301 (13,469 +831). That would be the value to continue downward until another value arose. This would reset when a new recordID
The ideal end result for each recordID would look similar to the image below
I may be pressing the envelope here, but is this something I could bury into a batch macro?
@Cbennett022 Thanks for the clarification. This is simpler than I originally thought. All you want is a running total of the book value, just filtered down to the current year. Because the data set includes prior year, this will ensure that the running total in January will include all prior year values.
The attached workflow still creates a template dataset for each record ID and date to account for missing months in the original data, but then does a running total and filters down to the current year only. I believe this gets you what you were requesting. While it could be done in a batch macro, it's not needed.