Fetching Data From the Different column

[Alteryxexpert]

I have data like below,

Date	Product	Sales	Forecast Sales
Jan-25	Chair	200	500
Feb-25	Chair	304	655
Mar-25	Chair	850	700
Apr-25	Chair	0	800
May-25	Chair	0	900
Jun-25	Chair	0	855
Jul-25	Chair	0	755
Aug-25	Chair	0	988
Sep-25	Chair	0	888

In sales whenever it is 0 i need to populate the previous month forecast sales value, ex for apr-25 I need to populate sales as 700(Mar-25 Forecast sales) like below, there are lot product in the product column for all this needs to be calculated in the same way.

Date	Product	Sales
Jan-25	Chair	200
Feb-25	Chair	304
Mar-25	Chair	850
Apr-25	Chair	700
May-25	Chair	800
Jun-25	Chair	900
Jul-25	Chair	855
Aug-25	Chair	755
Sep-25	Chair	988

How to do this in alteryx?

[davidskaife]

Hi @Alteryxexpert 

The Multi-Row Formula tool is the one to use for this, configured like below:

Note: ensure you tick Group By Product, and that your data is sorted in date order

[abacon]

@Alteryxexpert Use a multi-row formula tool.

If [Sales]=0 then [Row-1:forecast sales] else [forecast sales] endif.

This tool will take the previous row record of the value you choose.

Bacon

[caltang]

To visualise it:

Please add the group by element accordingly if you have Dates or Products... since it's a sample dataset, I don't know if there is this requirement or not. 

[AlteryxTrev]

As others have mentioned above, this can be done primarily with the Multi-Row Formula tool. However, I've taken it one step further to assure the data is in the correct order, since order matters when looking at adjacent rows.

1. convert your date (that is a sting) to a date field using the DateTime tool. You will need to use a Custom format to match you specific data (MMM-yy).

2. Sort your newly created date field ascending to guarantee your dates are in order. 

3. Use the Multi-Row Formula tool to populate your data accordingly. Namely, this can quality if something is true/false in the current row and depending on that outcome can populate it with values from another row.

  

[AlteryxTrev]

Here's a quick video I made explaining the above in a bit more detail. Hope it helps!

https://youtu.be/XLDtmfffWig