Look back and count the rows it took for a value to beat its previous level.

[akshaydalvicia]

I am learning Alteryx and trying to figure out how to count the number of rows backwards/upwards it took for a price to beat its previous prices in "n" rows. Set the count to 0 if the Price is less than previous row price.

For sample purposes, I kept few rows but let say i want to look back 250 rows (n) at a time, how can i achieve that in Alteryx? So my lookback limit is 250. Shorter limits are also okay, if anyone has a better solution to solve this problem in alteryx.

I have explained it in the below example in Notes/comments column for the reasoning of the expected behavior in Notes/Comments column.

example:

Date	Price	Expected output	Notes/Comments
11/12/2022	11	0	Why 0? coz no rows above to compare, Hence count is 0
11/13/2022	10	0	Why 0? coz 10 < 11 (previous price), hence count is 0
11/14/2022	13	2	Why 2? coz 13 > 10 (Previous price) and 13 > 11 (Previous to previous price), hence count is 2
11/15/2022	11	0	Why 0?  coz 11<13, hence count is 0
11/16/2022	14	4	Why 4? coz 14>11, 14>13, 14>10, 14>11, Hence count is 4
11/17/2022	12	0	Why 0?  coz 12<14, hence count is 0
11/18/2022	16	6	Why 6? coz 16>12, 16>14, 16>11, 16>13, 16>10, 16>11
11/19/2022	17	7	Why 7? coz 17> 16, 17>12, 17>14, 17>11, 17>13, 17>10, 17>11
11/20/2022	13	0	Why 0?  coz 13<17, hence count is 0

[DanielG]

Hi @akshaydalvicia 

Check this out.  It appears to work on the small sample.  Give it a shot on your larger dataset and let me know how it goes.

Thanks

[Yoshiro_Fujimori]

Hi @akshaydalvicia ,

To simplify the logic, I took the liberty of re-defining your requirement as below.

If the Price of the current row is the biggest value ever, set ([current row number] - 1).

Otherwise, set 0.

If this definition satisfies your needs, here is one way of doing it.

Workflow

Multi-Row Formula tool expression

[BiggestEver] = IF [Price] > [Row-1:BiggestEver] THEN [Price] ELSE [Row-1:BiggestEver] ENDIF

Formula tool expression

[IsBiggestEver]   = [Price] = [BiggestEver]

[Expected output] = IF [IsBiggestEver] THEN [RecordID] ELSE 0 ENDIF

Output

I hope this helps.

[akshaydalvicia]

Hi Yoshiro_Fujimori and DanielG.

Thank you for your post and I definitely learnt something new from each of them.

I am sorry, but i did not mean to change the goal-post here. The Price is not going to be the biggest ever. I made my sequence too simplistic.

Hence i added 1 more row item (last item) to the table where a current price can be > previous price but not necessarily the biggest price of the lookback (Let say 250 rows). Thanks for opening my eyes and allowing me to re-define this problem again.

Date	Price	Expected output	Notes/Comments
11/12/2022	11	0	Why 0? coz no rows above to compare, Hence count is 0
11/13/2022	10	0	Why 0? coz 10 < 11 (previous price), hence count is 0
11/14/2022	13	2	Why 2? coz 13 > 10 (Previous price) and 13 > 11 (Previous to previous price), hence count is 2
11/15/2022	11	0	Why 0?  coz 11<13, hence count is 0
11/16/2022	14	4	Why 4? coz 14>11, 14>13, 14>10, 14>11, Hence count is 4
11/17/2022	12	0	Why 0?  coz 12<14, hence count is 0
11/18/2022	16	6	Why 6? coz 16>12, 16>14, 16>11, 16>13, 16>10, 16>11
11/19/2022	17	7	Why 7? coz 17> 16, 17>12, 17>14, 17>11, 17>13, 17>10, 17>11
11/20/2022	13	0	Why 0?  coz 13<17, hence count is 0
11/20/2022	15	1	Why 1?  coz 15>13 but 15<17, hence count is 1

[Yoshiro_Fujimori · Accepted solution]

Hi @akshaydalvicia ,

No problem. It happens when testing with larger data set.

I think I could follow your needs with a little tweak.

The idea is that we need to remember which rows are biggest ever.

If the current row has the biggest price ever, the Row No (starting with 0) is the output.

Otherwise, the row count (starting with 0) after the latest "bigesst ever" is the output.

I hope this change fits your needs.

Workflow

Output

Multi-Row Formula tool (1)

BiggestEver = IF [Price] > [Row-1:BiggestEver] THEN [Price] ELSE [Row-1:BiggestEver] ENDIF

Multi-Row Formula tool (2)

RowNo_BiggestEver = IF [Price] = [BiggestEver] THEN [RecordID] ELSE [Row-1:RowNo_BiggestEver] ENDIF

Formula tool

Expected output = IF [Price] = [BiggestEver] THEN [RecordID] ELSE [RecordID] - [RowNo_BiggestEver] - 1 ENDIF

[DanielG]

@akshaydalvicia - @Yoshiro_Fujimori That is a great solution right there and shows why the multi-row tool is one of the best tools that Alteryx has to offer.  😊

[akshaydalvicia]

Hi  Yoshiro_Fujimori,

I accepted it as a solution. It unintentionally answered a question which i did not think of and this feature is also needed where i want to know how far a Price is from the "Biggest Ever Price" in the past?

My current problem is not Biggest Price ever in the past. My original intention is the first occurence of the price in the past (Descending Date lookback) which is Greater than Price in the current row. Take a look at the last row in the following table which i newly added:

Date	Price	Expected output	Notes/Comments
11/12/2022	11	0	Why 0? coz no rows above to compare, Hence count is 0
11/13/2022	10	0	Why 0? coz 10 < 11 (previous price), hence count is 0
11/14/2022	13	2	Why 2? coz 13 > 10 (Previous price) and 13 > 11 (Previous to previous price), hence count is 2
11/15/2022	11	0	Why 0?  coz 11<13, hence count is 0
11/16/2022	14	4	Why 4? coz 14>11, 14>13, 14>10, 14>11, Hence count is 4
11/17/2022	12	0	Why 0?  coz 12<14, hence count is 0
11/18/2022	16	6	Why 6? coz 16>12, 16>14, 16>11, 16>13, 16>10, 16>11
11/19/2022	17	7	Why 7? coz 17> 16, 17>12, 17>14, 17>11, 17>13, 17>10, 17>11
11/20/2022	13	0	Why 0?  coz 13<17, hence count is 0
11/21/2022	15	1	Why 1?  coz 15>13 but 15<17, hence count is 1
11/22/2022	16	2	Why 2? coz 16>15, 16>13, 16<17, hence the count is 2
11/23/2022	13	0	Why 0, coz 13 < 16
11/24/2022	14	1	Why 1? coz 14>13 but 14<16, hence the count is 1. Although 17 is the Biggest Ever but 16 on 11/22 is still Greater than 14, hence the expected output is 1 and not 5.

[Yoshiro_Fujimori · Accepted solution]

Hi @akshaydalvicia ,

Here you go. I hope it works.

To compare the price of each row with the older rows, Batch Macro comes in handy.

Main Workflow

Macro

Output

[akshaydalvicia]

Fantastic and Thank you so much @Yoshiro_Fujimori for being patient. All open threads close here related to this discussion. The large dataset humbled me everytime, i thought my example provided here was correct.

So much to learn from these Macros and workflows that you provided. Thanks a ton Appreciate it!