Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Dynamic Filter. automatic filer Until

Highlighted
8 - Asteroid

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

Highlighted
14 - Magnetar
14 - Magnetar

Hey @ZHENGCH!

 

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!

 

zhengch.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @ZHENGCH

 

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

Highlighted
8 - Asteroid

Hi Ben

 

Please find the attachment with the tab name Chart. I am using the dummy data to create such logic as well

 

Thanks

 

Chunbin

Highlighted
14 - Magnetar
14 - Magnetar

Hi @ZHENGCH

 

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.

Highlighted
8 - Asteroid

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

Highlighted
14 - Magnetar
14 - Magnetar

Of course @ZHENGCH!

 

So if you're not familiar with the IIF() statement, it is basically IIF(CRITERIAVALUE IF YOUR CRITERIA IS TRUEVALUE 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!

 

Highlighted
8 - Asteroid

@Kenda

 

That is great explanation. Really helpful.

 

Thanks


Chunbin

Highlighted
14 - Magnetar
14 - Magnetar

No problem @ZHENGCH

 

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.

Labels