community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Multi-row help

I have a table, similar to the first one below, that shows price changes over time. I need to take the initial price, add each price change to the previous price, then calculate the max price for each item. I've written out the basic steps in the tables below. 

 

Initial Data

 

DateItemPrice ChangeInitial Price
1/1/2019Strawberries 1.00
1/8/2019Strawberries0.05 
1/23/2019Strawberries-0.03 
2/1/2019Strawberries-0.05 
2/15/2019Strawberries0.02 
1/1/2019Bananas 0.78
1/12/2019Bananas0.03 
2/1/2019Bananas-0.05 
2/9/2019Bananas-0.01 
2/23/2019Bananas0.08 

 

Step 1: Calculate the New Price by adding the price change to the new price on the previous row.

 

DateItemPrice ChangeInitial PriceNew Price
1/1/2019Strawberries 1.00 
1/8/2019Strawberries0.05 1.05
1/23/2019Strawberries-0.03 1.02
2/1/2019Strawberries-0.05 0.97
2/15/2019Strawberries0.02 0.99
1/1/2019Bananas 0.78 
1/12/2019Bananas0.03 0.81
2/1/2019Bananas-0.05 0.76
2/9/2019Bananas-0.01 0.75
2/23/2019Bananas0.08 0.83

 

 

Step 2: Lastly, find the maximum price for each item from the New Price Column.

 

DateItemPrice ChangeInitial PriceNew PriceMax Price
1/1/2019Strawberries 1.00 1.05
1/8/2019Strawberries0.05 1.05 
1/23/2019Strawberries-0.03 1.02 
2/1/2019Strawberries-0.05 0.97 
2/15/2019Strawberries0.02 0.99 
1/1/2019Bananas 0.78 0.83
1/12/2019Bananas0.03 0.81 
2/1/2019Bananas-0.05 0.76 
2/9/2019Bananas-0.01 0.75 
2/23/2019Bananas0.08 0.83 

 

I'm pretty confident I need to use the multi-row formula for this one, possibly combined with the summary tool, but exactly how to do it escapes me. I've attached a workflow with the initial data in a text input. Thanks in advance!

Yes the approach I would take is what you said.

 

First use a Multi-Row formula grouped by Item with expression like:

IIF(IsNull([Initial Price]),[Row-1:NewPrice],[Initial Price]) 
+ 
IIF(IsNull([Price Change]),0,[Price Change])

Then use a summarise to get max by item and join back to the unsummarised data:

2019-03-02_22-08-47.jpg

 

Sample attached

Quasar

Attaching sample workflow for you case. Hope this is helpful. 

 

Multi-row help.PNG

Labels