Worst case calculation

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.

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))


  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?

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.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.