We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Calculate field based on row above

7 - Meteor

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
Amsterdamjan-22Mr. A2
Amsterdamfeb-22Mr. B0
Amsterdamapr-22Mr. C3
Amsterdamjun-22Mr. D1
Amsterdamjul-22Mr. E1
Amsterdamjul-22Mr. F5
Rotterdamapr-22Mr. X3
Rotterdamapr-22Mr. Y1
Rotterdammei-22Mr. Z7
Rotterdamjul-22Mrs. Z2

2 REPLIES 2
17 - Castor

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

Ira Watt
Technical Consultant
Watt@Bulien.com

12 - Quasar

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

Labels