Set Analysis in Expression (Chart)?

[#1]

Is it possible to use set analysis in an expression for a chart? I have many objects which are based on the same formular. If I need to change the formular I would have to change the formular in every single object.

To avoid this I would like to create an inline table which contains all formulars which I need. Now I can do this as it has been done in the KPI example which you can find here.

Regards

[pover · Accepted solution]

I assume you're referring to the problem that Set Analysis uses single and double quotes which can make importing the expressions more difficult. I've had success loading expressions from an Excel that contains all my expressions. The expression looks exactly as it would look in QlikView without having the replace double or single quotes with chr(34) or chr(39).

For example, I have an excel with 2 columns (Variable and Expression)

Variable
vSales

Expression
'sum({$<Year={$(=max(Year))},[Premisas y Conceptos Key] = {'$(=[Ingreso Key])'}, [Premisas y Conceptos Texto]=,FlagKey = {'$(=[Ingreso Cubo Key])'}, FlagText=,[Version Key] = , [Version Texto]={'Real'},[Unidad de Negocio Texto]={$(vUniNegFiltro)},[Unidad de Negocio Key]=>} Importe)

Then in the script, I turn every row of the Excel into a variable so that I can use $-expansion in the charts and not use macros.

Script Example:

Expresiones:
LOAD Variable,
Expresion
FROM
Expresiones.xlsx
(ooxml, embedded labels, table is Sheet1);

Let vNroRegistros = NoOfRows('Expresiones');

For vI = 0 to (vNroRegistros - 1)

Let vNombre_Variable = Peek('Variable',vI,'Expresiones');

Let [$(vNombre_Variable)] = Peek('Expresion',vI,'Expresiones');

Next

Then in the expression of the chart I just put

$(vSales)

I hope that helps. Regards.

[#3]

Karl,

Very helpful.

is the single quote in front of your expression intentional?

Thanks,

Rich

[pover]

Yes, it's to make sure Excel doesn't consider the formula an Excel formula. In newer versions of Excel it is automatic and it isn't actually part of the data. QlikView will read the cell starting with sum(... without the single quote.

Regards.

[#5]

Exactly what I was looking for. I was just a bit struggling with the spanish variable names inside your script. So I have just made an 'international' version of if just in case anybody in the future is facing the same problem.

Expressions:
LOAD Variable,
Expression
FROM
C:\Temp\TestFormula.xlsx
(ooxml, embedded labels, table is Table1);
Let vNumberOfRows = NoOfRows('Expressions');
For vI = 0 to (vNumberOfRows - 1)
Let vVariable_Name = Peek('Variable',vI,'Expression');
Let [$(vVariable_Name)] = Peek('Expression',vI,'Expression');
Next

[#6]

Karl,

Is there a way to drop to unneeded variables after completing the load?

I mean the variables -> vNombre_Variable -> vl -> vNroRegistros

Thanks,

Rich

[pover]

Richard,

Post another post to see if there is another answer because I think this can only be done with a marco:

Sub Delete_Variable
 ActiveDocument.RemoveVariable "vName"End Sub

[#8]

Thanks Karl,

I think I'll live with it.

I'm try to stay away from macros.

Rich

[#9]

Can't you do like

Set vName=;

[pover]

Yeap, you are correct, Johan.