Worst case calculation

[hbarrera_c]

Hi, I'm looking for help for the following issue, hope you could help me.

Suppuose the table:

week	col1	col2	val1	val2	kpi
1	a	lbl1	9304	9542	0.97505764
1	a	lbl2	9616	9734	0.987877543
1	a	lbl3	9573	9684	0.988537794
1	b	lbl4	9606	9611	0.999479763
1	b	lbl5	9270	9492	0.976611884
1	b	lbl6	9657	9850	0.980406091
2	a	lbl1	9281	9787	0.948298764
2	a	lbl2	9634	9690	0.994220846
2	a	lbl3	9801	9966	0.983443709
2	b	lbl4	9718	9786	0.993051298
2	b	lbl5	9588	9669	0.991622712
2	b	lbl6	9619	9739	0.987678406

where kpi=val1/val2

I need global value like this:

week	col1	Suma de val1	Suma de val2	KPI
1	a	28493	28960	0.983874309
1	b	28533	28953	0.985493731
2	a	28716	29443	0.975308223
2	b	28925	29194	0.990785778

The next step, with this data is to find the worst : for example for week 1 and case A we have kpi = 0.9838 as global value, but we need to know the worst offender, so for this case I calculate excluding one by one label data so we found: If I exclude lbl1 the kpi increase, having 0.9882(lbl2&lbl3) vs 0.9838 (lbl1&lbl2&lbl3)

Label	Suma de val1	Suma de val2	KPI
Without lbl1	19189	19418	0.988206818
Without lbl2	18877	19226	0.981847498
Without lbl3	18920	19276	0.981531438

This procedure its the same for all weeks and all labels.

Hope you could help me with ideas, how can I develop a script in R.

Best Regards.

[jrkrideau]

I think that this will do the first part

dat1 <-   structure(list(week = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
2L, 2L), col1 = c("a", "a", "a", "b", "b", "b", "a", "a", "a", 
"b", "b", "b"), col2 = c("lbl1", "lbl2", "lbl3", "lbl4", "lbl5", 
"lbl6", "lbl1", "lbl2", "lbl3", "lbl4", "lbl5", "lbl6"), val1 = c(9304L, 
9616L, 9573L, 9606L, 9270L, 9657L, 9281L, 9634L, 9801L, 9718L, 
9588L, 9619L), val2 = c(9542L, 9734L, 9684L, 9611L, 9492L, 9850L, 
9787L, 9690L, 9966L, 9786L, 9669L, 9739L), kpi = c(0.97505764, 
0.987877543, 0.988537794, 0.999479763, 0.976611884, 0.980406091, 
0.948298764, 0.994220846, 0.983443709, 0.993051298, 0.991622712, 
0.987678406)), class = "data.frame", row.names = c(NA, -12L))

library(tidyverse)

  dat1 %>%   mutate( kpi = val1 / val2) %>% 
     group_by(week, col1) %>% 
      summarise(sum1 = sum(val1), sum2 = sum(val2), kp1 = sum(kpi))
  

but I am not clear on the second part.

Do you want to exclude the lowest value in a week By col1 subset in the raw data and tend do the summing?

[hbarrera_c]

Thanks for your help.

Suppose this is a performance kpi for unavailable cpu's, in this case
val1 = power on time and
val2= power off time,

As you explain to me in part one calculate the global performace per week and location (a,b), the second part consist in calculate or know the worst offender, for example for week 1, location "a" I could know cpu1 (lbl1) is the most contributor for kpi downgrade, so, if I improve the cpu1 performance the global kpi will increase.

But for this calculation (week 1, location "a" ) I need to calculate 3 times the global one, excluding in each calculation one cpu per time.

Hope I could explain the purpose of second part.