Dimension value as dynamic variable in expression

Anonymous — Fri, 02 Feb 2018 03:03:45 GMT

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

2.5%

=$(vCode12)

ABC1

1500

ABC2

1200

1.5%

ABC3

2000

ABC4

5000

0.2%

ABC5

2500

0.4%

0.2%

ABC6

1200

ABC7

7000

0.5%

0.1%

ABC8

1600

1.25%

0.25%

ABC9

4500

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

Re: Dimension value as dynamic variable in expression

Shilpa16 — Mon, 01 Jul 2019 20:02:25 GMT

Hi Martin,

Please advise if you were able to resolve this issue?

Regards,

Nitin

topic Dimension value as dynamic variable in expression in QlikView

Dimension value as dynamic variable in expression

Re: Dimension value as dynamic variable in expression