For each customer, get the prior row values and input in latest row's fields

[007udon]

Hello! I'm unsure if I can solve this in a workflow or if it requires some sort of macro, or if there's a term to describe my question that I can use in my online search for similar posts - 

I have a dataset that's listing out all customers over several quarters, along with information about their subscription purchases, subscription end quarter, the quarter in which they purchased it, and prices. Each quarter, a customer will purchase only one or two different type of subscriptions. And as subscription term ends, sometimes customers will renew and purchase again (which produces another record in the dataset for that quarter).

What I'm trying to do is automate a way to fill in field "Prior Subscription $" for each row. If it was a new customer, then the value would be left blank (or a value of 0 would also work). If it was not a new customer, I wanted to extract the prior purchase's "Subscription $" value.

I tried to put together a small dataset example with two customers and two products, and the "Prior Subscription $" I want to automate. It feels like I'd need to somehow iterate through each customer, and go through their purhcase history in desc order, then somehow assign their latest row's "Prior Subscription $" value to equal their second row's "Subscription $".

Bonus if I can do the same thing, but be able to extract the prior value of any other fields, for example, Prior Purchase Quarter.

Many thanks!

 

[Luke_C · Accepted solution]

Hi @007udon 

A multi-row formula tool grouped by customer/product should get you there, at least with this sample dataset:

[007udon]

Thank you so much, this was really helpful!