Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rennesia
New Contributor III

Set analysis with variable in expression

For analysis I need a result depending expression.
The expressions has to be: 
    =avg({$} DISTINCT DailyProduction)

or:

     =avg({1} DISTINCT DailyProduction)

In a variable vSelect I move the character 1 or $.

I thought that I could use the following expression:

     =avg({=$(vSelect)} DISTINCT DailyProduction)

but unfortunately this expression results always in an error.

Does anyone know a solution for this problem?

1 Solution

Accepted Solutions
MVP
MVP

Re: Set analysis with variable in expression

Seems to be working for the sample I created. Check it out:

=Count({$(=vSelect)}DailyProduction)

or

=Count({$(vSelect)}DailyProduction)

12 Replies
MVP
MVP

Re: Set analysis with variable in expression

Try this:

=Avg({$(=vSelect)} DISTINCT DailyProduction)

or

=Avg({$(=$(vSelect))} DISTINCT DailyProduction)


rennesia
New Contributor III

Re: Set analysis with variable in expression

Thanks Sunny, but both solutions give the 'feared' red line under the text DISTINCT DailyProduction).
When I show the results in a text object, the returning text is: Error in expression: '}' expected.

That is also the returning text from my own solution...

MVP
MVP

Re: Set analysis with variable in expression

Or maybe just

=Avg( {$(vSelect)} DISTINCT DailyProduction)

It depends on how you set your variable.

The Magic of Dollar Expansions

The Magic of Variables

The Little Equals Sign

rennesia
New Contributor III

Re: Set analysis with variable in expression

Thanks also Swuehl, but this solution has the same error in result.

Nice documents! I'll read them next days (weekend) and try next week.

MVP
MVP

Re: Set analysis with variable in expression

Seems to be working for the sample I created. Check it out:

=Count({$(=vSelect)}DailyProduction)

or

=Count({$(vSelect)}DailyProduction)

Not applicable

Re: Set analysis with variable in expression

test this possibility

=IF(vSelect = 1, Avg({ 1 } DISTINCT DailyProduction) , Avg({ $ } DISTINCT DailyProduction))

MVP
MVP

Re: Set analysis with variable in expression

Unfortunately, the syntax checker is not really reliable when it comes to e.g. dollar sign variable expansions (similar issues also for examples with implicite set operators).

2016-06-04 01_40_23-Edit Expression.png

Just click OK and check if it's working. I would assume it does work, if your variable is set just to 1 or $ as shown in Sunny's sample.

rennesia
New Contributor III

Re: Set analysis with variable in expression

Nope, it doesn't work...
I read the document 'The magic of dollar expansions' and have to conclude (in combination with my tests) that a $ as textual variable is not the same as a direct $ in the function. (Quote: 'A dollar expansion is something that is done before the expression (or statement) is evaluated.')
The only way to get my result is write out the complete syntax as Claiton Luiz suggested.
Thanks for your help!

Highlighted
rennesia
New Contributor III

Re: Set analysis with variable in expression

Thanks for this answer. It think that this is the only solution for the moment. Read also my reply to Swuehl below...