Creating formula that references a fiscal (non-traditional calendar) week

IBG_rs

I'm having difficulty writing a working formula to assess sales performance inactivity/risk for our sales team. I'm trying to create a formula that will look to see how may weeks (based on our corporate fiscal calendar) have passed since a sales agent has made a sale. 1-3 weeks would return a risk value of "Low", 4-6 as "Moderate", 7-9 as "High", and 10-13 as "Critical." The values "Low, Moderate, High, Critical" would appear in "Submit Risk" column.

The real difficulty I'm experiencing in writing the "Weeks since last submit" column formula is trying to reference the past 13 weeks of business based on our fiscal calendar (not a traditional calendar).

Once that formula returns a correct value I need "Submit Risk Level" column formula that assigns the risk value based on # of weeks since last "submit" (week with submitted business).

Our week column (Called FH Week) follows this format:

Wk 01 - Dec 25 (JAN#1)
Wk 02 - Jan 1 (JAN#2)
Wk 03 - Jan 8 (JAN#3)
Wk 04 - Jan 15 (JAN#4)
Wk 05 - Jan 22 (FEB#1)
Wk 06 - Jan 29 (FEB#2) …etc.

Additionally, all sales activity for the year appears chronologically on this sheet. As such, the formula should only return results based on the past 13 weeks for the Agent Name on that row (not an aggregate of all agents for the past 13 weeks).

Here is a screenshot for reference:

Thanks, in advance, for your help in writing these two column formulas.

jmyzk_cloudsmart_jp

Hi @IBG_rs

I added helper columns in my demo solution, [Week #] and [Last Submt # Week Ago].

"To see how many weeks (based on our corporate fiscal calendar) have passed since a sales agent has made a sale", we can use the following formula;

=[Week #]@row - MAX(COLLECT([Week #]:[Week #], [Agent Name]:[Agent Name], [Agent Name]@row, Submit:Submit, 1))

Here, the COLLECT function returns a range of the Week # of the [Agent Name]@row, and Submit is 1. The MAX function returns the largest such Week #. If you subtract the MAX(COLLECT()) value from the current Weed #, you will get the [Last Submit # Week Ago].

Then, with the IF function, you can get the [Submit Risk Level] with the following formula;

=IF([Last Submt # Week Ago]@row < 4, "Low (0-3)", IF([Last Submt # Week Ago]@row < 7, "Moderate (4-6)", IF([Last Submt # Week Ago]@row < 10, "High (7-9)", "Critical (10-13)")))

Mine uses the checkbox for the Submit column, but the result is the same as if you use 1 or 0 because the checked checkbox is a 1 or true value.

You can check how the formula works by scrolling down the sheet below to see the last Josh's and Janet's sales submit or change the submit checkbox.

https://app.smartsheet.com/b/publish?EQBCT=e1df2b47c418494e98182ad9e7b18652

IBG_rs

Thank you for your helpful ideas. You are very skilled at functions, so I'm sure you'll be able to help correct the remaining error. That is, there isn't a formula to calculate the number of weeks that have passed since there was a "submit" or "sale" (shown by a checkmark or "1"). First, there needs to be a "weeks since last submit" column added.

Using your published example, if you added a "weeks since last submit" and made a filter to only look at John, you would see that John made sales (submits) in weeks 11 and 12 but not in weeks 13-22. Therefore:

• In the week 13 row the "weeks since last submit" should say 1 (because he made a submit the previous week, but not week 13)
• In week 14 the "weeks since last submit" should say 2 (because he last made a submit two weeks ago)
• In week 14 the "weeks since last submit" should say 3 (and so on)
• But John made a sale in In week 23. So on week 24 it should say "1" for weeks since last submit.

Once we have the "weeks since last submit" column formula working, the "Submit Risk Level" formula should work as intended.

IBG_rs

Update. I was able to use Chat GPT (with several rounds of back and forth to correct errors) to get it to work. Here is the advice it provided (which closely resembles what jmyzk_cloudsmart_jp was trying to advise, I believe:

We need a formula that correctly handles the calculation by looking at the most recent submission week before the current week. Here’s a revised approach:

1. Create a Helper Column: First, create a helper column named "Last Submit Week" to store the fiscal week number of the last submit for each row.
2. Helper Column Formula: Use this formula in the "Last Submit Week": =MAX(COLLECT([Fiscal Week Number]:[Fiscal Week Number]
3. Weeks Since Last Submit Formula. Use the following formula in the "Weeks since last submit" column: =IF(Submit@row = 1, 0, [Fiscal Week Number]@row - [Last Submit Week]@row)

Steps in Smartsheet

1. Add Helper Column: Add a new column named "Last Submit Week" to your sheet.
2. Apply Helper Column Formula:
   • =MAX(COLLECT([Fiscal Week Number]:[Fiscal Week Number], [Agent Name]:[Agent Name], [Agent Name]@row, [Fiscal Week Number]:[Fiscal Week Number], < [Fiscal Week Number]@row, Submit:Submit, 1))
   • Drag this formula down the column to apply it to all rows.
3. Apply Weeks Since Last Submit Formula:
   • =IF(Submit@row = 1, 0, [Fiscal Week Number]@row - [Last Submit Week]@row)
   • Drag this formula down the column to apply it to all rows.

Verification

After applying these formulas, verify that the "Weeks since last submit" values are correct. This method ensures that you are referencing the correct fiscal week number of the last submit for each agent.

If the calculations are still incorrect, please let me know, and I can provide further assistance.