Dynamic Filter. automatic filer Until

cbz

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

Dynamic Filter.docx

Kenda

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!