Hi
I am trying to do a dynamic filter logic (automatic on a monthly base)
Where i need to Filter From" Actuals 2017" to "Forecast 2018" and From "Forecast 2018" to "Budget 2019"
and the number of rows in between might change month by month where i dont want to do manual selection every month. is there any solution for this?
Please find the attachment for the issue
Thanks
Chunbin
Solved! Go to Solution.
Hey @cbz!
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:
iif([SubCat]="Actuals 2017" || [SubCat]="Forecast 2018",[Row-1:New Field]+1,[Row-1:New Field])
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:
iif([SubCat]="Actuals 2017" || [Row-1:SubCat]="Forecast 2018",[Row-1:New Field]+1,[Row-1:New Field])
Hope this helps!
Hi @cbz
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.
Ben
Hi @cbz
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.
Hi@BarnesK
That really works! but would you mind explain to me how does this formula work?
and how does it return with 1 and 2?
IIF([SubCategory]="Actuals 2017"||[Row-1:SubCategory]="Forecast 2018", [Row-1:NewField]+1, [Row-1:NewField])
Thanks
Chunbin
Of course @cbz!
So if you're not familiar with the IIF() statement, it is basically IIF(CRITERIA, VALUE IF YOUR CRITERIA IS TRUE, VALUE IF YOUR CRITERIA IS FALSE)
In our case, the criteria is
[SubCategory]="Actuals 2017"||[Row-1:SubCategory]="Forecast 2018"
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.
[Row-1:NewField]+1
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.
[Row-1:NewField]
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.
Let us know if you still have questions!
No problem @cbz!
If you think the Multi-Row Formula tool could come in handy in the future and want to learn more about it, check out this article.