Formula based on week number

[BrianHausman]

Hi, I am looking to trend score a vendor based on service level in a given week.  In the data below you see I have 10 weeks of service level data.  I would like to add two columns, one that shows weeks where any of the ten weeks have a level less that 95% and one where any of the last two weeks has a level under 90%.  I have tried to rank the weeks but that has been unsuccessful.  The first dataset is my actual the second is how I would like it to show.

Week	Vendor Name	Service Level
01/09/2022	Vendor 1	92.37%
01/16/2022	Vendor 1	97.68%
01/23/2022	Vendor 1	96.28%
01/30/2022	Vendor 1	98.43%
02/06/2022	Vendor 1	97.81%
02/13/2022	Vendor 1	93.80%
02/20/2022	Vendor 1	94.52%
02/27/2022	Vendor 1	95.65%
03/06/2022	Vendor 1	95.83%
03/13/2022	Vendor 1	98.64%
01/09/2022	Vendor 2	92.48%
01/16/2022	Vendor 2	92.25%
01/23/2022	Vendor 2	91.75%
01/30/2022	Vendor 2	90.22%
02/06/2022	Vendor 2	90.47%
02/13/2022	Vendor 2	92.27%
02/20/2022	Vendor 2	91.83%
02/27/2022	Vendor 2	91.61%
03/06/2022	Vendor 2	93.24%
03/13/2022	Vendor 2	91.64%
01/09/2022	Vendor 3	95.43%
01/16/2022	Vendor 3	90.68%
01/23/2022	Vendor 3	89.07%
01/30/2022	Vendor 3	90.82%
02/06/2022	Vendor 3	90.79%
02/13/2022	Vendor 3	88.95%
02/20/2022	Vendor 3	81.64%
02/27/2022	Vendor 3	80.69%
03/06/2022	Vendor 3	75.72%
03/13/2022	Vendor 3	75.65%

Week	Vendor Name	Service Level	10 Week	2 Week
01/09/2022	Vendor 1	92.37%	1
01/16/2022	Vendor 1	97.68%
01/23/2022	Vendor 1	96.28%
01/30/2022	Vendor 1	98.43%
02/06/2022	Vendor 1	97.81%
02/13/2022	Vendor 1	93.80%	1
02/20/2022	Vendor 1	94.52%	1
02/27/2022	Vendor 1	95.65%
03/06/2022	Vendor 1	95.83%
03/13/2022	Vendor 1	98.64%
01/09/2022	Vendor 2	92.48%	1
01/16/2022	Vendor 2	92.25%	1
01/23/2022	Vendor 2	91.75%	1
01/30/2022	Vendor 2	90.22%	1
02/06/2022	Vendor 2	90.47%	1
02/13/2022	Vendor 2	92.27%	1
02/20/2022	Vendor 2	91.83%	1
02/27/2022	Vendor 2	91.61%	1
03/06/2022	Vendor 2	93.24%	1
03/13/2022	Vendor 2	91.64%	1
01/09/2022	Vendor 3	95.43%
01/16/2022	Vendor 3	90.68%	1
01/23/2022	Vendor 3	89.07%	1
01/30/2022	Vendor 3	90.82%	1
02/06/2022	Vendor 3	90.79%	1
02/13/2022	Vendor 3	88.95%	1
02/20/2022	Vendor 3	81.64%	1
02/27/2022	Vendor 3	80.69%	1
03/06/2022	Vendor 3	75.72%	1	1
03/13/2022	Vendor 3	75.65%	1	1

Any ideas would be appreciated.

Brian

[mceleavey · Accepted solution]

Hi @BrianHausman ,

Nice and simple. You first need to convert the Service Level to a numeric field, and then simply apply an IF function to determine if that number is below 95.

Then, isolate the last two weeks of each Vendor group using the sample tool, do the same IF using 90 as the figure, then join back and tidy up. Simple.

This gives the following results:

Workflow attached.

I hope this helps,

M

[gabrielvilella]

Hi @BrianHausman, here is how you can achieve this.

[atcodedog05 · Accepted solution]

Hi @BrianHausman 

Here is my take on it.

Workflow:

Hope this helps : )

[gabrielvilella]

@atcodedog05 Are we competing on who does this with less tools? 😁

[atcodedog05]

Hi @gabrielvilella 

I wouldn't say competing because I always love to learn from others than compete 😅 

And partly challenge myself to see how to solve it with lesser tools/easier method 😅😁

[mceleavey]

@atcodedog05 and @gabrielvilella fewer tools are not an indicator of elegance ;)

M