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]

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

[ahaahaaha]

Hi Mats,

May be like this

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

Regards,

Andrey

[Anonymous]

Hi Andrey,

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

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]

Try

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

[neelamsaroha157]

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

[vishsaggi]

May be this?

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

[Anonymous]

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]

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.