Fetching Data From the Different column

Question

I have data like below,

DateProductSalesForecast SalesJan-25Chair200500Feb-25Chair304655Mar-25Chair850700Apr-25Chair0800May-25Chair0900Jun-25Chair0855Jul-25Chair0755Aug-25Chair0988Sep-25Chair0888

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.

DateProductSalesJan-25Chair200Feb-25Chair304Mar-25Chair850Apr-25Chair700May-25Chair800Jun-25Chair900Jul-25Chair855Aug-25Chair755Sep-25Chair988

How to do this in alteryx?

AlteryxTrev · Answer

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

https://youtu.be/XLDtmfffWig

AlteryxTrev · Answer

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.

caltang · Answer

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.

For alteryxexpert.yxmd