topic Re: Using set analysis within a variable (and the variable is an expression imported from Excel) in QlikView

Using set analysis within a variable (and the variable is an expression imported from Excel)

Thu, 16 May 2013 19:55:06 GMT

Hi all,

I have a list of expression names and formulars that I am importing in from Excel

Expression	Formula
Expression name 1	formula with set analysis
Expression name 2	different formula with set anlysis

I have then set up my variables such that vExpression = only(Formula).

This allows me to have a drop down list of which variable to pick and means I need less graphs.

Currently, this breaks down if I use set analysis in the formula.

Does anyone know how to over come this?

Mnay thanks,

Isabel

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

rustyfishbones — Thu, 16 May 2013 20:02:54 GMT

We need to see the Formula

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

anandathome — Thu, 16 May 2013 20:22:21 GMT

Please paste your formulae here in the discussion.

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

CELAMBARASAN — Thu, 16 May 2013 21:11:41 GMT

Is that necessary to give expressions in drop down?

Do your client really need to see the formula you are using?

Check with Whats new in qlikview 11 to use dynamic expressions and dimensions.

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

Thu, 16 May 2013 21:14:56 GMT

From memory, the formulas are like:

Sum({$<[field to exclude]=>} [field to sum])

Now these set analysis formulas work in a table before I copy them into Excel. ( and the purpose of putting them into Excel is so that I have a drop down list of expressions that work in a variable). Now, the variables do work just fine if I take out the set analysis, but if course, I want them in to exclude certain selections!

Please say if this is not enough detail and the I will add in more with formulas/expressions in the morning.

Thanks,

Isabel

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

CELAMBARASAN — Thu, 16 May 2013 21:16:42 GMT

Check by removing that $ symbol.

Because qlikview sees the $ symbol and tries to execute it. For the qlikview it is invalid script so it will show error or null.

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

Thu, 16 May 2013 21:17:46 GMT

From memory, the formulas are like:

Sum({$<[field to exclude]=>} [field to sum])

Please say if this is not enough detail and the I will add in more with formulas/expressions in the morning.

Thanks,

Isabel

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

MayilVahanan — Thu, 16 May 2013 21:18:38 GMT

Try like this

=$(=variableName)

Hope it helps

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

rustyfishbones — Thu, 16 May 2013 21:27:19 GMT

You may have 2 = signs when you take the formula from Excel do you have it like

=Sum({$<[field to exclude]=>} [field to sum])

and are you then calling the Expression as

=vExpression

This could be the problem

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

Thu, 16 May 2013 21:29:04 GMT

Yes, I believe a drop down is necessary:

I wish to have one graph (it is actually linked to a map with OpenLayers) and let my client select the expression name to plot. They won't see the formula, only the expression name, but my variable is the formula linked to the expression.

There is a possibility that it is my map that will not function with set analysis so I shall test this out first thing.

I will also try the suggestion about removing the $ sign.

Isabel

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

Thu, 16 May 2013 21:31:45 GMT

Hi Allan,

That was a good idea, but I don't have the = in the Excel Formula so sadly this isn't the solution!

Thanks though!

Isabel

Re: Using set analysis within a variable (and the variable is an expression imported from Excel)

Fri, 17 May 2013 07:19:22 GMT

The issue appears to be with the OpenLayers extension not accepting the set analysis, not infact the variable which work.

I shall open up a new discusison to see if anyone knows how to get around that!

Isabel