Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rennesia
Contributor III
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
sunny_talwar

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

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

or

=Count({$(vSelect)}DailyProduction)

View solution in original post

12 Replies
sunny_talwar

Try this:

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

or

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


rennesia
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

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.

sunny_talwar

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

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

or

=Count({$(vSelect)}DailyProduction)

Not applicable

test this possibility

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

swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

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!

rennesia
Contributor III
Contributor III
Author

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