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.
If I'm understanding your question and data correctly, I think you need to make use of the Multi-Row Formula tool. As you'll see below, I created a new field, grouping on Chart, and using the following expression:
This way, from Actuals 2017 to the record before Forecast 2018, the new field has a value of 1. Then, from Forecast 2018 until Budget 2019, the new field has a value of 2. From here, you can filter and do whatever other processing you need.
If you want Forecast 2018 in the same grouping as Actuals 2017, just change the expression to this:
For sure there is a way to achieve this, but could you please share some sample data in a data file format (for example excel), the word doc is useful but would still require a lot of effort from ourselves to copy your scenario data.
The formulas I provided in my first post should work for your whole data set. The records labelled with 1's will be your Actuals 2017 through Forecast 2018 rows and the records labelled with 2's will be your Forecast 2018 rows through the Budget 2019 rows.
This checks to see if the current row's SubCategory value is "Actuals 2017" OR the previous row's SubCategory value is "Forecast 2018."
If either of these statements are true, the value for the new field's current row will be the previous row's new field value plus one.
If neither of the statements in your criteria are true, the false statement will prevail. Here that means the current row's new field value will be the same as the previous row's.
We do this because we want a new grouping created each time we see the values "Actuals 2017" or "Forecast 2018" in the SubCategory field. The new group is created by adding one to the previous row's value, as you see in the above formulas.
The 'Values for Rows that don't Exist' is set to be 0 or Empty in this case. This means that the row just before the first row (which doesn't actually exist) will have a value of 0. By adding 1 to this, the value of our first row will be equal to 1.