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 Designer Desktop Discussions

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

Calculate field based on row above

jvanderpol
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,

 

 

jvanderpol_1-1662122757721.png

 

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
IraWatt
17 - Castor
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:

IraWatt_0-1662123498882.png

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