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 · Accepted solution]

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%20... 

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Sebastiaandb]

He @jvanderpol ,

Bijgevoegd een voorbeeld workflow :-).

Het gevaarlijke in je dataset is dat je de maanden hebt in het Nederlands, Alteryx kent geen vertaling vanuit Nederlandse dates naar een Engelse date kolom om de sortering juist te krijgen :-). Je zou dit nog kunnen aanpassen door simpel een formule toe te voegen die alle Nederlandse maandafkortingen wijzigt in de Engelse.

Succes!

Greetings,

Seb