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

[ShankerV]

Hi @FilipViterra 

Please use multi row formula to check the diff between previous available amount and current line.

If yes, then 1

Else 0.

Then Sort Descending based on the new column created by multi row formula.

Result arrived in 2 tools.

Can you please paste the input in copy and paste format. so I can build the workflow also if needed.

Many thanks

Shanker V

[DataNath · Accepted solution]

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.

[grazitti_sapna]

@FilipViterra , here is the workflow to get your desired output. Please check this and let me know if it was helpful.

[FilipViterra]

Thank you for the help :), it wasn't exactly the desired output but it helped me understand better what to do!

[FilipViterra]

Thanks a lot! It works perfectly!