Hi everyone,
I’m going slightly mad over a task that I’m trying to solve.
I want to build 6 KRI that will indicate the level of risk based on combinations of different data sources that varies pretty much in size but doesn't change in a larger scale over time.
The KRIs are combination of 3 data sources for each KRI, some of the data sources will appear in several KRI and weighted differently. In addition to these KRI I want an aggregated (and weighted) KRI that is based on all KRI resulting in a number between 1-100 (no score can be lower than 1 or higher than 100).
I have tried to normalize, transform log, standardize, etc. with no luck. Is this even possible and if so, how do I achieve this without macros? Any other ideas to achieve the same thing are welcomed.
___________________
Data sources value (DATA 1 - DATA 10): 232, 565545, 4533, 342, 54, 6899, 134323, 4344, 567, 14322
Aggregate data into KRI (weights in parentheses)
KRI 1: DATA 1 (0,5), DATA 2 (0,3), DATA 3 (0,2)
KRI 2: DATA 2 (0,5), DATA 7 (0,25), DATA 1 (0,25)
KRI 3: DATA 5 (0,35), DATA 1 (0,4), DATA 9 (0,25)
KRI 4: DATA 2 (0,4), DATA 7 (0,4), DATA 8 (0,2)
KRI 5: DATA 4 (0,5), DATA 9 (0,25), DATA 3 (0,25)
KRI 6: DATA 1 (0,5), DATA 10 (0,25), DATA 6 (0,25)
Aggregated KRI resulting in a number between 1-100 based weighted aggregated KRI (weights in parentheses)
KRI 1 (0,15)
KRI 2 (0,2)
KRI 3 (0,2)
KRI 4 (0,2)
KRI 5 (0,15)
KRI 6 (0,1)
I’m going slightly mad over a task that I’m trying to solve.
I want to build 6 KRI that will indicate the level of risk based on combinations of different data sources that varies pretty much in size but doesn't change in a larger scale over time.
The KRIs are combination of 3 data sources for each KRI, some of the data sources will appear in several KRI and weighted differently. In addition to these KRI I want an aggregated (and weighted) KRI that is based on all KRI resulting in a number between 1-100 (no score can be lower than 1 or higher than 100).
I have tried to normalize, transform log, standardize, etc. with no luck. Is this even possible and if so, how do I achieve this without macros? Any other ideas to achieve the same thing are welcomed.
___________________
Data sources value (DATA 1 - DATA 10): 232, 565545, 4533, 342, 54, 6899, 134323, 4344, 567, 14322
Aggregate data into KRI (weights in parentheses)
KRI 1: DATA 1 (0,5), DATA 2 (0,3), DATA 3 (0,2)
KRI 2: DATA 2 (0,5), DATA 7 (0,25), DATA 1 (0,25)
KRI 3: DATA 5 (0,35), DATA 1 (0,4), DATA 9 (0,25)
KRI 4: DATA 2 (0,4), DATA 7 (0,4), DATA 8 (0,2)
KRI 5: DATA 4 (0,5), DATA 9 (0,25), DATA 3 (0,25)
KRI 6: DATA 1 (0,5), DATA 10 (0,25), DATA 6 (0,25)
Aggregated KRI resulting in a number between 1-100 based weighted aggregated KRI (weights in parentheses)
KRI 1 (0,15)
KRI 2 (0,2)
KRI 3 (0,2)
KRI 4 (0,2)
KRI 5 (0,15)
KRI 6 (0,1)
