Hi all,
I am struggling with a complicated count that involves summing a row of data.
I have raw data of product transactions, that details the total transactions across a defined period
The data then details how many customers bought 1 of the product, 2 of the product etc
It is displayed in rows as below
Product Identifier Total Transactions Pareto MPQ Quantity Bought in a Transaction
1 2 3 4 5 6 7 8 9 10 11 12
Product A 101 2040 E 7 5 6 8 9 12 300 1500 200 0 0 0 0
Product B 102 1611 D 7 5 6 2 5 15 452 654 6 56 5 5 400
Product C 103 4515 C 10 1 5 2 46 5 5 5 546 1300 1500 1100 0
Product D 104 2009 B 1 2000 1 1 1 1 0 1 1 1 1 1 0
Product E 105 18497 A 3 1 8000 10000 200 100 50 52 32 31 30 1 0
what I am trying to do is define a rule that says you should always have x of a product to satisfy a service level for your customers
i.e. to satisfy 100% of product a customers I need to have 9 in stock at all times
but 90% of customers I could have 8 in stock
the rules are
Pareto Service Level
A 90%
B >70% and <90%
C >65% and <=70%
D >60% and <=65%
E <=60%
ALL 100%
what I'd like to do is populate a grid with
A B C D E All
product A 10 8 5 3 1 12
This tells me for Pareto A type products i need to hold 10, but if I set it to 8 I need to change the pareto to B or 100% of customers I need to always have 12
I recognise that in some instances I would have the same value, for A and B. also that I may have multiple values for a given threshold, but in these instances I want to choose the higher value
Can anyone help
R
I am struggling with a complicated count that involves summing a row of data.
I have raw data of product transactions, that details the total transactions across a defined period
The data then details how many customers bought 1 of the product, 2 of the product etc
It is displayed in rows as below
Product Identifier Total Transactions Pareto MPQ Quantity Bought in a Transaction
1 2 3 4 5 6 7 8 9 10 11 12
Product A 101 2040 E 7 5 6 8 9 12 300 1500 200 0 0 0 0
Product B 102 1611 D 7 5 6 2 5 15 452 654 6 56 5 5 400
Product C 103 4515 C 10 1 5 2 46 5 5 5 546 1300 1500 1100 0
Product D 104 2009 B 1 2000 1 1 1 1 0 1 1 1 1 1 0
Product E 105 18497 A 3 1 8000 10000 200 100 50 52 32 31 30 1 0
what I am trying to do is define a rule that says you should always have x of a product to satisfy a service level for your customers
i.e. to satisfy 100% of product a customers I need to have 9 in stock at all times
but 90% of customers I could have 8 in stock
the rules are
Pareto Service Level
A 90%
B >70% and <90%
C >65% and <=70%
D >60% and <=65%
E <=60%
ALL 100%
what I'd like to do is populate a grid with
A B C D E All
product A 10 8 5 3 1 12
This tells me for Pareto A type products i need to hold 10, but if I set it to 8 I need to change the pareto to B or 100% of customers I need to always have 12
I recognise that in some instances I would have the same value, for A and B. also that I may have multiple values for a given threshold, but in these instances I want to choose the higher value
Can anyone help
R
