Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik lovers, I have an interesting case that I came across and I hope you can help me solve it.
For this case, I am trying to filter against a numeric field [KPI_Code]
I created a Table and inserted [KPI_Code] as a Dimension.
I have 2 expressions, the results are in the table below
Expression A: Max(KPI_Code)
Expression B: Aggr(Only({<KPI_Code={"$(=Max(KPI_Code))"}>}KPI_Code),KPI_Code)
when I use a dollar sign expansion, it always gives me the maximum number in the dataset, disregarding the dimension value.
How can I use $() and still get values just like in Expression A ???
------------
Note: I need to use the $() because I need the output value inside a variable to call its expression
Example: vKPI_$()_Expression
if the $() resulted in 1001 then the variable would become vKPI_1001_Expression and I would use it's saved expression output in the cell beside its corresponding KPI_Code
KPI_Code | Expression A | Expression B |
1004 | 1004 | |
1001 | 1001 | - |
1002 | 1002 | - |
1003 | 1003 | - |
1004 | 1004 | 1004 |
=Aggr(Max({<KPI_Code={"$(=Max(KPI_Code))"}>} KPI_Code), KPI_Code)
Th $-expansion creates an adhoc-variable which could have only a single value at a time - and this is calculated before the chart and then applied to all rows.
Beside this you couldn't any condition within a set analysis which needs to respect the dimension-values because it worked like a selection on a column-level. To consider any row-level conditions you will need an if-loop.
In your case you may need an expression like:
if(Code = 1001, $(vKPI_1001), if(Code = 1002, $(vKPI_1002), ...))
which is probably not what you want to do.
I suggest that you consider to change the approach completely and moving the expression-logic into the dimensional layer within the data-model.