topic Re: Set expression, Aggr() and variables in QlikView

Set expression, Aggr() and variables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 13:22:30 GMT

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

Re: Set expression, Aggr() and variables

ahaahaaha — Wed, 31 May 2017 13:24:44 GMT

Hi Mats,

May be like this

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

Regards,

Andrey

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 13:33:33 GMT

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

Re: Set expression, Aggr() and variables

ahaahaaha — Wed, 31 May 2017 13:37:29 GMT

Try

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

Re: Set expression, Aggr() and variables

neelamsaroha157 — Wed, 31 May 2017 13:41:07 GMT

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

Re: Set expression, Aggr() and variables

vishsaggi — Wed, 31 May 2017 13:47:45 GMT

May be this?

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

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 13:54:19 GMT

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

sunny_talwar — Wed, 31 May 2017 13:57:49 GMT

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

Re: Set expression, Aggr() and variables

ahaahaaha — Wed, 31 May 2017 14:08:01 GMT

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.

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 14:09:14 GMT

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.

Re: Set expression, Aggr() and variables

sunny_talwar — Wed, 31 May 2017 14:15:26 GMT

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])

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 14:20:54 GMT

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.

Re: Set expression, Aggr() and variables

sunny_talwar — Wed, 31 May 2017 14:24:31 GMT

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

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 14:30:13 GMT

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

Re: Set expression, Aggr() and variables

sunny_talwar — Wed, 31 May 2017 14:39:18 GMT

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?

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 14:42:30 GMT

exactly

Re: Set expression, Aggr() and variables

sunny_talwar — Wed, 31 May 2017 14:51:32 GMT

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]))

Re: Set expression, Aggr() and variables

Anonymous — Wed, 31 May 2017 15:03:16 GMT

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?

Re: Set expression, Aggr() and variables

sunny_talwar — Wed, 31 May 2017 15:07:03 GMT

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]))