Set expression, Aggr() and variables

[Anonymous]

Dear Community,

I am using the following expression to calculate the average revenue (revenue=$(eUmsatz)) of a group of companies (Management IDs):

=Money(Avg(Aggr($(eUmsatz),[Management ID])), '#.###,##€', ',', '.')

This works just fine.

I would now like to further narrow down the results by using set expression.

The field is "Scope" and the value I would like to limit the expression to and then, in another object, exclude is "Scope Q2".

The Set Analysis Wizard suggests: =Avg({$<Scope={'Scope Q2'}>}$(eUmsatz))
I still need to add the Aggr() though. Can anyone tell me how the full expression looks like?

Thank you in advance!

Best regards

Mats

[Anonymous]

Now with the expression you provided I can display the average revenue for a number of companies that are in scope (Scope Q2).

The business user now wants to compare this revenue (displayed in the text box) with the individual revenues of all the companies that are NOT in scope. The user wants to identify out of scope companies that perform really poorly, compared to the average of the in scope companies.

Does that makes sense?

[Anonymous]

Scope -= {'Scope Q2'}

... works as supposed as long as Scopt "contains" no null() values...!

[Anonymous]

Now with the average revenue set to scope Q2 I can also use the action button so that the table shows the individual values for the not in scope companies anyway. I was just interested in the syntax to exclude the 'Scope Q2' companies with set expression.

[Anonymous]

Hi Robin, that's the issue, Scope contains only the Scope Q2 values AND null values.

it's like

company A          Scope Q2

company B          -

company C          -

company D         Scope Q2

[Anonymous]

I know that problem very well

try something like

Only({$-<Scope ={'Scope Q2'}>} ...)

or

Only({

(<[Management ID]=>)

*

($-<Scope ={'Scope Q2'}>)

} ...)

[Anonymous]

Do we always use only() when we "just" want to add set expression to an already existing variable?

I've tried

Only({$-<Scope ={'Scope Q2'}>} $(eUmsatz))

which is a nested aggregation.

When I add Aggr..

=Only({$-<Scope ={'Scope Q2'}>} Aggr($(eUmsatz), ...))

the Aggr() expects a dimension to aggregate over. I don't really want to aggregate $(eUmsatz) over anything though, as I want to display $(eUmsatz) for every row in the chart.

[sunny_talwar · Accepted solution]

You can aggregate over your chart dimension or add the set analysis to your variable....

[Anonymous]

I got it now, thank you.

Thanks again to everyone for putting in time and effort.

[Anonymous]

I just like to add a comment, a variable within a dollar expansion $() or $(#)  or $(=) is nothing more than a substitute for a string or a number (or both if dual() ).

So if QlikView calculates the expression the variable doesn't exist anymore...