Roll updates forward

FilipViterra

Hello everyone,

I have a small issue I can't really solve.

The situation is that we have different farmers, reporting crops from every season. This crops are available to buy, and get updates in time. See example below: 

Here we have 2 farmers, 1 from Hungary, one from Romania. The hungarian farmer has 2100 metric tons in February 2021, later, in June 2021 this amount goes down to 750 metric tons. Similarly, the romanian farmer reports 5000 metric tons of corn in July 2022, and later in September 2022 this amount raises to 6000 metric tons.

What I'm trying to achieve is something liek below:

Basically, we have the reported amount of 2100 MT for the hungarian farmer in February 2021, i want to roll that amount forward until another update is provided, like the one in June of 750 metric tons, which becomes the new amount available. If there are no other updates provided, roll it to the month we are in, in our example hanuary 2023.

Can someone please help? I struggled with it for a while, and I'm a bit lost.

Thanks a lot,

Filip

DataNath

Hey @FilipViterra how does something like this look?

The steps here are to:

1) Convert the date field into ISO format that Alteryx can work with (YYYY-MM-DD)

2) Pull the date from the next row (grouped by Country/Farmer)

3) Generate rows until we hit the next update (if there's no next update, we generate up until the current month)

4) Select to tidy up the data again

I had to mock up some data quickly using shortened field names etc. as you didn't provide your actual sample, so you'll just need to change the field names in the formulae in the attached workflow.

Roll farming data forwards.yxmd