Calculate field based on row above

jvanderpol

I am trying to do the following:

I have a table of location, order-date, customer and # ordered. Per location, the first x (x = 5) will be delivered and that one will be fully delivered.

In excel I would order based on location and date, then - as long as city is the same - add the # ordered to the number above. How would I do this in Alteryx.

The final step - only select up to the first one exceeding 5 - is an easy if-statement in Alteryx.

Any suggestions?

Thanks and kind regards,

location	Date of order	Person	# ordered
Amsterdam	jan-22	Mr. A	2
Amsterdam	feb-22	Mr. B	0
Amsterdam	apr-22	Mr. C	3
Amsterdam	jun-22	Mr. D	1
Amsterdam	jul-22	Mr. E	1
Amsterdam	jul-22	Mr. F	5
Rotterdam	apr-22	Mr. X	3
Rotterdam	apr-22	Mr. Y	1
Rotterdam	mei-22	Mr. Z	7
Rotterdam	jul-22	Mrs. Z	2

IraWatt

Hey @jvanderpol,

To calculate based on rows above and below you can use the Multi Row Formula Tool more info on that here: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872 

However you are just doing a running total, luckly there is a tool just for that called the running total tool:

After that you can just use a formula at add the extra column.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20Expressions 

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

Workflow.yxmd