How to index a specific month to Month 0 and have each subsequent month go up 1

[farrerb]

Hello,

I have a list of a thousand locations (each row) with monthly revenue (in columns labeled with a calendar month). Each location has a different open date. I would like to have a workflow that can create a file to index the open month to the month in which the location opened and every subsequent month be designated month 1, month 2, etc...

The idea being, across the 1000 locations, I should be able to identify and compare what was revenue in month 1 (the first full month after opening) across all locations, no matter when the location had opened.

I tried to look for a similar solution, but had a bit of a hard time searching through keywords. Seems like a common financial analysis problem, so if there's a separate topic where this is discussed, please point me in the direction.

Thanks!

[Luke_C]

Hi @farrerb 

This definitely should be possible, probably by transposing the month columns and using a multi-row or tile tool to assign the index. Can you share some sample data and expected results?

[farrerb]

@Luke_C - thanks for the quick response! I put together a simplified dummy dataset, but should conceptually work as an analogue for my dataset. One tab for the raw data and one for the expected outcome.

[Qiu]

@farrerb 
Take my shot  before @Luke_C  and hope @Luke_C will not mind. 😁

[farrerb]

@Qiu - very close, but I put in an intentional nuance as I have identified it in the dataset and I assumed it would add complexity to the calculation.

Location 4 doesn't have any revenue in Month 0. So, in your output, Month 0 for Location 4 should actually be blank, and Month 1 should be 2.

[Qiu · Accepted solution]

@farrerb 
Sorry about that.
Acutuall I was wondering that also and took a shortcut. 😁

[farrerb]

Thanks @Qiu . I'll work with the team tomorrow with this workflow in mind and see if it'll fit with the live dataset I have. However, clearly your output matches my dummy output so I consider this one solved. I'll add a comment if there's additional nuances in the live dataset I didn't simulate with the dummy dataset. Thanks again.

[Qiu]

@farrerb 

Thank you for the feedback and have a good day. 😁

[farrerb]

@Qiu - quick question. What's the purpose of the "a" column? It appears to be the month that the location opened, but I don't understand how its used. I see "Col" is the indexed month, so will definitely need that column, but just wondering if the "a" column can be stripped out.

[Qiu]

@farrerb 
You are right, the "a" column might be something left over from the revision. Sorry about that.