I have a table of thousands of records, each with one of 10 codes, ABC0 - ABC9. I have straight table which counts these, as well as the number that have closed in the last 12 months based on a closed date available. The values have KPIs that only allow for 2% closure in any 12 month period, or 10% over 20 years. I can calculate these based on total count and temporal variation.
I'm trying to create a situation where people can edit a variable to model the effect on KPIs if X number were closed.
| | ClosedCount | | | | Modelled 12 month loss | Modelled total loss |
|---|
| ABC0 | 1000 | 30 | 3% | 25 | 2.5% | =$(vCode12) | |
| ABC1 | 1500 | 50 | 3% | 15 | 1% | | |
| ABC2 | 1200 | 60 | 5% | 18 | 1.5% | | |
| ABC3 | 2000 | 40 | 2% | 40 | 2% | | |
| ABC4 | 5000 | 10 | 0.2% | 10 | 0.2% | | |
| ABC5 | 2500 | 10 | 0.4% | 5 | 0.2% | | |
| ABC6 | 1200 | 96 | 8% | 36 | 3% | | |
| ABC7 | 7000 | 35 | 0.5% | 7 | 0.1% | | |
| ABC8 | 1600 | 20 | 1.25% | 4 | 0.25% | | |
| ABC9 | 4500 | 45 | 1% | 15 | 0.3% | | |
So if I were to allow users to input a number for closures for each of the codes ABC0-9, for 12 month and total closures, would they breach the KPIs? I've been able to create dynamic variables that I can use within the expression, but they are reliant on the selection of a code (ABC0-9) and they apply the same value across all the other codes. I've done this by applying a variable for each code (i.e. vABC0_12, vABC0_Total, ... vABC9_12, vABC9_Total) that the users can modify. In the expression,
I can create an expression that calls the variable name as text, but it doesn't bring the variable value unless i use the $-expansion within a nested variable, in which case it only works when a single code is selected.
vCode12 ='v' & [AttributeTypeCode] & '_12'
where user sets vABC0_12 to equal 50, such the 2% KPI would be 5%.
Any ideas how I can make this work across all codes in the dimension?
Cheers,
Martin
