Target average in a set of values

[JT0168]

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!      

[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 · Accepted solution]

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