Given below sample data.... No.SKULeadtime (days)166500850266480020366479910466470585655296766552587765468368616682696093405106064545116059455128524531364332214209811 Set Target Average Lead time = 5days; so from No. 4 to No 17 the average is 5, which formula to use in Alteryx to get those SKU that did not meet the target, which is from NO. 1 to No. 3. Thank you!

Target average in a set of values

Given below sample data....

No.	SKU	Leadtime (days)
1	665008	50
2	664800	20
3	664799	10
4	664705	8
5	655296	7
6	655258	7
7	654683	6
8	616682	6
9	609340	5
10	606454	5
11	605945	5
12	85245	3
13	64332	2
14	20981	1

Set Target Average Lead time = 5days; so from No. 4 to No 17 the average is 5, which formula to use in Alteryx to get those SKU that did not meet the target, which is from NO. 1 to No. 3.

Thank you!

Workflow

Optimization

Expression

Accepted answers

IraWatt

Hey @JT0168,

One way to do this is with an iterative macro. It loops through averaging from the lowest Lead time to highest and stops looping once the average is above 5. It then adds a column indicating which row tipped the balance over, in our case row 12 or no.3.

If you want to learn more about Macros the community has some really quick interactive videos on getting to grips with them here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Example.yxzp

All comments

IraWatt

@JT0168 Why are No. 4 to No 17 averaged, they are all different SKU's ?

JT0168

@IraWatt - sorry supposed to be No.14 only and not 17. I am looking for those SKU which affects the overall average lead time target of 5 for a specific category. So definitely after averaging No.4 to No 14 the average is 5, when you include No. 3 the new average is 5.4, if again you add No 2 the new average is 6.54.....and if No. 1 is included the new lead time for the data is 9.64..so definitely No. 1 to No. 3 affects the target lead time.

IraWatt

Hey @JT0168,

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Example.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703