topic AW:Re: Set Analysis in Expression (Chart)? in QlikView

Set Analysis in Expression (Chart)?

Mon, 15 Nov 2010 10:27:53 GMT

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

Set Analysis in Expression (Chart)?

pover — Mon, 15 Nov 2010 18:20:54 GMT

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.

Set Analysis in Expression (Chart)?

Mon, 15 Nov 2010 18:56:01 GMT

Karl,

Very helpful.

is the single quote in front of your expression intentional?

Thanks,

Rich

Set Analysis in Expression (Chart)?

pover — Mon, 15 Nov 2010 19:39:19 GMT

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.

AW:Re: Set Analysis in Expression (Chart)?

Tue, 16 Nov 2010 08:13:04 GMT

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

Set Analysis in Expression (Chart)?

Wed, 17 Nov 2010 16:36:53 GMT

Karl,

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

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

Thanks,

Rich

Set Analysis in Expression (Chart)?

pover — Wed, 17 Nov 2010 17:04:08 GMT

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

Set Analysis in Expression (Chart)?

Wed, 17 Nov 2010 17:27:23 GMT

Thanks Karl,

I think I'll live with it.

I'm try to stay away from macros.

Rich

Set Analysis in Expression (Chart)?

Wed, 16 Mar 2011 19:43:30 GMT

Can't you do like

Set vName=;

Set Analysis in Expression (Chart)?

pover — Fri, 18 Mar 2011 04:08:44 GMT

Yeap, you are correct, Johan.

Re: Set Analysis in Expression (Chart)?

andrefpc — Fri, 02 Aug 2013 14:07:46 GMT

Karl is it possible to use this with diferent expressions, without using uncountable if clauses? I'm struggling with this.

Thanks in advance.