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.
12 month variation
Modelled 12 month loss
Modelled total loss
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?