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.
AttributeCode
StartingCount
ClosedCount
10% KPI
12 month variation
2% KPI
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?
