topic Re: Set analysis with variable in expression in QlikView

Set analysis with variable in expression

rennesia — Fri, 03 Jun 2016 13:48:34 GMT

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?

Re: Set analysis with variable in expression

sunny_talwar — Fri, 03 Jun 2016 13:53:02 GMT

Try this:

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

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

Re: Set analysis with variable in expression

rennesia — Fri, 03 Jun 2016 14:05:05 GMT

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...

Re: Set analysis with variable in expression

swuehl — Fri, 03 Jun 2016 14:17:32 GMT

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

Re: Set analysis with variable in expression

rennesia — Fri, 03 Jun 2016 14:28:28 GMT

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.

Re: Set analysis with variable in expression

sunny_talwar — Fri, 03 Jun 2016 15:17:30 GMT

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

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

=Count({$(vSelect)}DailyProduction)

Re: Set analysis with variable in expression

Fri, 03 Jun 2016 16:50:57 GMT

test this possibility

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

Re: Set analysis with variable in expression

swuehl — Fri, 03 Jun 2016 23:44:12 GMT

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).

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.

Re: Set analysis with variable in expression

rennesia — Mon, 06 Jun 2016 07:04:41 GMT

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!

Re: Set analysis with variable in expression

rennesia — Mon, 06 Jun 2016 07:07:20 GMT

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

Re: Set analysis with variable in expression

swuehl — Mon, 06 Jun 2016 10:42:07 GMT

Strange.

Isn't Sunny's sample QVW working for you just fine and demonstrating that the dollar sign expansion is working?

Using what I've suggested above:

=Count({$(vSelect)}DailyProduction)

and a simple input box that changes the variable between the two literals:

I still assume it's all about how you combine the dollar sign expansion with your variable definition.

Seems like you don't want to share how you exactely do this, so I won't insist on offering more help.

Regards,

Stefan

Re: Set analysis with variable in expression

rennesia — Tue, 07 Jun 2016 11:39:33 GMT

Stefan,

Indeed, the entire document may not be distributed ... So I made yesterday a small test document to post at the community. But, the problem was not reproducible! Everything worked fine.
Conclusion: I will erase a part of the original document and start again.

Thanks for your help.

Re: Set analysis with variable in expression

rennesia — Tue, 07 Jun 2016 11:43:52 GMT

Thanks for help Sunny. I tried several times with bad results, but when I decide to make a new document, everything works fine. (See also my last reaction to Swuehl at Jun 7.)