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]

Hi Sunny,

the variable $(eUmsatz) is   

(Sum({$< [INCOMESTATEMENT_TEXT] = {'Net sales total'}>}[Period Value GC]) )

You actually helped me with the action button for the 'Scope' selection a while ago, thanks again.

[sunny_talwar]

May be try this

Sum({$< [INCOMESTATEMENT_TEXT] = {'Net sales total'}, Scope={'Scope Q2'}>}[Period Value GC])

or this

Sum({$< [INCOMESTATEMENT_TEXT] = {'Net sales total'}, Scope>}[Period Value GC])

[Anonymous]

You are right, it's difficult to explain.

Please find the app attached. The object with the expression is the blue Text Box in the middle.

What I would like to achieve is to compare the average revenue for companies that are in scope ('scope Q2')

with the revenue of individual companies that are not in scope (null). You can switch by clicking the action buttons.

[sunny_talwar]

What is the expected output based on the selection of buttons? Do you need a single text box object or 2 text box objects?

[Anonymous]

just a single text box per average KPI for the in scope companies (like the ones at the top)

the table should then list the KPIs for the individual out of scope companies

maybe it's not even necessary to use the action buttons because this comparison is all that's needed

[sunny_talwar]

So you are already seeing the blue text box to show the Scope Q2 things, do you want this number to stay when you select Not in scope Q2? Is this the requirement?

[Anonymous]

exactly

[sunny_talwar]

May be this

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

and variable

(Sum({$< [INCOMESTATEMENT_TEXT] = {'Net sales total'},  [Management ID]>}[Period Value GC]))

[Anonymous]

Well this seems to work just fine, I think I'll create an additional variable for this as I am using $(eUmsatz) for other dimensions than [Management ID] as well. Thanks a lot!

Now, for the table with the companies that are not in scope and where I don't need an average..
Can I just use E() with the original variable to show the individual revenues?

Something like    ( {$<Scope = E({1<Scope={‘Scope Q2’}>})>} $(eUmsatz) ) or similar?

[sunny_talwar]

For exclusion you can do this

Scope -= {'Scope Q2'}

but once again what is the expected output here? Tried this, but this isn't giving anything

Only({<Scope -={'Scope Q2'}, [Management ID]>} Aggr($(eUmsatz),[Management ID]))