Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
martin_n
New Contributor

Dimension value as dynamic variable in expression

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 lossModelled total loss
ABC0

1000

303%25

2.5%

=$(vCode12)
ABC11500503%151%
ABC21200605%181.5%
ABC32000402%402%
ABC45000100.2%100.2%
ABC52500100.4%50.2%
ABC61200968%363%
ABC77000350.5%70.1%
ABC81600201.25%40.25%
ABC94500451%150.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

1 Reply
Shilpa16
New Contributor

Re: Dimension value as dynamic variable in expression

Hi Martin,

Please advise if you were able to resolve this issue?

Regards,

Nitin