Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
masch112
Contributor

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

Re: Set expression, Aggr() and variables

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

28 Replies
masch112
Contributor

Re: Set expression, Aggr() and variables

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

ahaahaaha
Honored Contributor

Re: Set expression, Aggr() and variables

Hi Mats,

May be like this

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


Regards,

Andrey

masch112
Contributor

Re: Set expression, Aggr() and variables

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
Honored Contributor

Re: Set expression, Aggr() and variables

Try

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

neelamsaroha157
Valued Contributor II

Re: Set expression, Aggr() and variables

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

vishsaggi
Esteemed Contributor III

Re: Set expression, Aggr() and variables

May be this?

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

masch112
Contributor

Re: Set expression, Aggr() and variables

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.

Re: Set expression, Aggr() and variables

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

ahaahaaha
Honored Contributor

Re: Set expression, Aggr() and variables

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.