Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set expression, Aggr() and variables

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



1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

28 Replies
Anonymous
Not applicable
Author

PS: The variable includes Sum(), that is why Aggr() is needed.

ahaahaaha
Partner - Master
Partner - Master

Hi Mats,

May be like this

=Money(Avg(Aggr({$<Scope={'Scope Q2'}>} $(eUmsatz),[Management ID])), '#.###,##€', ',', '.')


Regards,

Andrey

Anonymous
Not applicable
Author

Hi Andrey,

I've actually tried this but the result still adjusts to the selection in the "Scope" field.


Capture.JPG

It says Expression OK but still marks part of the set expression as red.

Could the set expression part be at the wrong place?

Thank you.

Regards,

Mats

ahaahaaha
Partner - Master
Partner - Master

Try

=Money(Avg({$<Scope={'Scope Q2'}>} Aggr($(eUmsatz),[Management ID])), '#.###,##€', ',', '.')

neelamsaroha157
Specialist II
Specialist II

It would be helpful if you can share some sample data & expected output.

vishsaggi
Champion III
Champion III

May be this?

=Money(Avg({< Scope = {'Scope Q2'} >} Aggr($(eUmsatz), [Management ID])), '#.###,##€', ',', '.')

Anonymous
Not applicable
Author

The syntax is right now, thank you!

Do you know if it's possible to also disregard the selections that are made in the field Scope for this expression? And just in this field.

I am using an action button to switch between "Scope Q2" and the excluded values in Q2 in this field (NULL).

Whenever I switch to the excluded values, the expression doesn't return a value anymore, which is probably

expected because the set expression still considers all of my selections.

sunny_talwar

What is your expression behind $(eUmsatz), you need to add the set analysis within this variable's expression also

ahaahaaha
Partner - Master
Partner - Master

Yes, you are right, Set Analysis expression overrides all your choices in field Scope  by the value Scope = Scope Q2. Maybe then try to use condition If similar to this

If(Condition1, Expression with Scope = Scope Q2, another Expression with your selection).

As variant, for Condition1 above expression If, you can create a variable whose value is changed each time you click button (add this action to the button macro).

But in general it is difficult to give exact advice without seeing your application.