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.
I am having issues automating my process. I currently have a process that pulls a historical YXDB and adds current month data to it, that is all automated and good to go. My issue now comes in with my calculations, as with a new month, I need to manually add the next month.
So for example; my data is structured like this:
I have a formula tool right now that does this;
This is where my issue comes in, being May, I now need to go in and update the process for a P4 Out_of _Stock
I'd like to have a multi-row formula do something like this:
Where if the current Field Name contains "Out_of_Stock" go find the field with the same Months Label; P1 for example, and divide by that field. However, as I am posting this, that formula doesn't work. I know the above formula would not work once I got to months 10 and 11, but for the sake of seeing if this is even possible, let's assume months end at 9 🙂
This may not be possible in the way I have my data structured, but I thought if anyone could help it would be the community!
I have attached the workbook with the above tools in it!
@willcogs26 First, @fmvizcaino's solution is a good one and the way I would do it as well. Second, I have an idea of adding an eval function that would be perfect for this situation. Third, I just wanted to add that the dynamic replace is another option for a use case like yours: