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.