Adding a fiscal week to a column

[avavalley]

Hi everyone,

Each week a new set of data is inserted under the previous week's set of data in my model.  I am hoping to put a column to the left that will update by fiscal week automatically ex: FW34, FW35, FW36, FW37, FW38 etc.  Not sure how to go about this as it is a combination of letters and numbers instead of just a week#.

[neilgallen]

There are a number of different ways to go about doing this, but without seeing your model or data, here's what I'd recommend:

From your existing data set a summarize tool would get you the maximum week number. This becomes your new starting point for the new data.

From there, use a formula tool to add one to this number, and then create a new string that is "FW"+[field] which is your current number.

Lastly, just append this new field to every record in your new data and combine to your existing data set.

This all assumes that you don't actually have a date field in your data which you could use to derive the Fiscal week from, which would be a cleaner route. The above solution assumes that there would be no gaps from week to week with updates.

[avavalley]

Hi Neil,

Thanks this helps! I do already have a column I am just looking to fill it as shown in the sheet I have uploaded.

[danilang · Accepted solution]

Hi @avavalley 

You can use something like this.  Note: that I deleted FW14 from your input file

Filter for the rows where Week is not null and use the summarize tool to get the last of these values.  Calculate the next fiscal week with the following formula

"FW" + tostring(tonumber(Substring([Last_Week],2))+1)

Working from the inside out, this formula extracts the number portion from last_week, converts it to a number, adds 1 and then converts this to a string so it can be concatenated with "FW".

Take this and append it to the original data and use a formula to replace the week with the new week if it's null

Dan

[avavalley]

This worked perfectly! thanks