Dimension values as formula in expression chart

[renaudfeuvrier]

Hi,

Is it possible to use the values of a dimension as formulas in expression chart ?

I've got an excel table, that I ll load in a script :

INDICATOR	SORT	FORMULA
IND1	1	vFormula1
IND2	2	vFormula2
ETC	…	…

vFormula1, vFormula2 are variables which contain expression formula as : Count({< ... >} Customer).

I would like to use this table to build a straight table, using INDICATOR as dimension sort by SORT, and FORMULA in the expression. 

What I've got to write in the formula to compute the expression. 

I tried "=FORMULA " and "Only(FORMULA)", that s display the formula not the result.

I also tried "=$(=FORMULA)" and '=$(=Only(FORMULA))", this display nothing.

Thank you for your time.

[PiEye · Accepted solution]

Hi Renaud

The problem with set analysis is it is evaluated in the context of the chart, and not on every row, ie it is calculated once for the whole object and that result is what is passed to each row.

So =$(=Only(FORMULA)) will not evaluate, because over the whole chart there are many formulas, and not "only" one and this will evaluate to null.

You will have to work around the issue.

The simplest way is to use an if() or pick() expression to choose the formula you need based on what is in the row

IF(Formula = 'vFormula1', $(vFormula1), IF(Formula = 'vFormula2',$(vFormula2)....

or

pick(right(formula,1),$(vFormula1),$(vFormula2)...

However these can be memory intensive.

What are in the formulas? sometimes you can be clever and link the indicators using data modelling or count using flags in the data model instead

Pi

[renaudfeuvrier]

Hi Pi,

Thank for the answer.

I understand the problem with the Only.

I ve got to produce more than 80 indicators with the same dimensions but a lot of differences for the expression in only one table. I thought that it could be more maintanable if i could manage it with an Excel file. 

Thanks again.

Renaud

[PiEye]

What is the nature of the differences?  I've done similar before with indicators and have been able to manage with clever flags and linked fields.

Could you give any samples, or something close enough, to preserve confidentiality.

Pi

[renaudfeuvrier]

This report is destinated to the human ressource. It s covering a large spectrum from the turn over to the to the training passing throught the absenteeism. I m not able to communicate that s kind of data.

Your answer permit me to clarify the uses of meta expression in Qlik.

[PiEye · Accepted solution]

Hi Renaud, if that is the case  please can you mark my answer as correct?

Thank you

Pi