topic Re: Aggr in Set analysis in QlikView

Aggr in Set analysis

vipingarg23 — Sat, 02 Sep 2017 18:18:42 GMT

I am trying to exclude some fields using set analysis with aggregation function

I am using below sample expression

Sum(Aggr(sum({$<A={'3'},B={'AP'}>}Total),dim))

I want to apply Aggr function only for B instead of both (A,B). Is it possible?

Re: Aggr in Set analysis

Oleg_Troyansky — Sat, 02 Sep 2017 18:22:23 GMT

I don't think you fully understand the role of AGGR and the role of Set Analysis.

Can you please describe the analytical problem that you are trying to solve, in non-technical terms?

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: Aggr in Set analysis

vipingarg23 — Sat, 02 Sep 2017 18:27:34 GMT

Please see below thread..

https://community.qlikview.com/thread/214906

I want like this, but I am not getting this expression completely.

Re: Aggr in Set analysis

sunny_talwar — Sat, 02 Sep 2017 19:04:03 GMT

I want to apply Aggr function only for B instead of both (A,B). Is it possible?

What does this statement means? Can you elaborate?

Re: Aggr in Set analysis

vipingarg23 — Sun, 03 Sep 2017 06:08:33 GMT

I just want to know that If we have two expression(separated by comma) in

single set analysis and If I want to apply aggr function only for one

expression, then Is it possible?

On Sun, Sep 3, 2017 at 12:34 AM, Sunny Talwar <qcwebmaster@qlikview.com>

Re: Aggr in Set analysis

karthikoffi27se — Sun, 03 Sep 2017 08:49:45 GMT

Hi Vipin,

Please use the below expression

=Sum({$<A={'3'}>}Total)+Sum(Aggr(sum({$<B={'AP'}>}Total),dim))

Many Thanks

Karthik

Re: Aggr in Set analysis

sunny_talwar — Sun, 03 Sep 2017 17:27:53 GMT

Your response just don't make any sense... can you elaborate with an example?

Re: Aggr in Set analysis

vipingarg23 — Mon, 04 Sep 2017 17:00:54 GMT

Hi,

I am refraining my question in different way again:

I am using below expression:

=sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total)*(

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total))-

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Total)))

This expression is working fine. but If I use this expression with AGGR as

below, it is not giving me right data:

Sum(Aggr(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total)*(

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total))-

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Total))),dim))

Can you please confirm if this syntax is fine

On Sun, Sep 3, 2017 at 10:58 PM, Sunny Talwar <qcwebmaster@qlikview.com>

Re: Aggr in Set analysis

Kushal_Chawda — Mon, 04 Sep 2017 17:15:57 GMT

if expression is working fine then why you are using Aggr?

Re: Aggr in Set analysis

MarcoARaymundo — Mon, 04 Sep 2017 17:22:28 GMT

Try this

1) Create a variable "vDIM" and put this exprecion:

if(GetSelectedCount(A) > 0, 'A') &

if(GetSelectedCount(A) > 0 and GetSelectedCount(B) > 0, ',') &

if(GetSelectedCount(B) > 0, 'B')

2) Put vDim variable into your expression Sum(Aggr(sum({$<A={'3'},B={'AP'}>}Total), $(vDIM)))

Re: Aggr in Set analysis

sunny_talwar — Mon, 04 Sep 2017 19:56:53 GMT

Not sure what you are after, but may be this

Sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))', '$(=$(Var3))'}>}Aggr(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total)*(

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var2))'}>}Total))-

(sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Cost)/sum({$<A={'<=$(=$(Var1))'},Year={'$(=$(Var3))'}>}Total))),dim))

Re: Aggr in Set analysis

vipingarg23 — Tue, 05 Sep 2017 04:45:24 GMT

Great Sunny. I was stuck in the same since long. You made my day. Thank you very much for your help.

Re: Aggr in Set analysis

vipingarg23 — Tue, 05 Sep 2017 04:49:45 GMT

Sunny..One thing, I just want to know that what is the difference between both expression. Should we always use the expression (Which you told) in AGGR with set analysis or Is there any other logic behind it?

Re: Aggr in Set analysis

sunny_talwar — Tue, 05 Sep 2017 08:56:05 GMT

Well the outer aggregation should include the same set analysis that is needed for the inner one else the row wise number might match, but the total from the outer aggregation may filter out things....

Re: Aggr in Set analysis

vipingarg23 — Tue, 05 Sep 2017 09:22:33 GMT

Thanks for clarification sunny..