topic aggr? in QlikView

aggr?

Marc_Kaiser — Fri, 25 Mar 2011 12:56:43 GMT

Hi there

I have the follwowing source table and have not found yet an approach how to achieve the expected result:

Source table
MainGroup Subgroup Group Member Value
Main1 Sub1 Group1 Tony 100
Main1 Sub2 Group2 Andrew 100
Main1 Sub3 Group3 Tony 100
Main2 Sub1 Group4 Tony 200
Main2 Sub2 Group5 Tony 200

The value is related to MainGorup and Member - no matter what Subgroup or Group is present.
No matter how many Subgroups / Groups are present, the member only pays once per MainGroup.

So, the following result table is expected.

Result table
MainGroup Member Value
Main1 Tony 100
Main1 Andrew 100
Main2 Tony 200
Sum 400

Any idea how to set up the needed expression?

Thanks for your help!

Marc

aggr?

Fri, 25 Mar 2011 13:10:00 GMT

Hi Marc Kaiser,

I got your problem. make the expression like the below.

=sum(distinct {MainGroup & Member}, Value)

=sum(distinct{MainGroup and Member},value)

if it is not working, please kindly ignore this reply..

aggr?

pat_agen — Fri, 25 Mar 2011 13:18:55 GMT

Hi Marc,

if the value is always teh same for a MainGroup then using sum(distinct Value) in th expression will do the trick.

look at the attached qvw.

aggr?

suniljain — Fri, 25 Mar 2011 13:19:00 GMT

Load

Maingroup,

Member,

sum(Value) as Value

Resident Sourcetable group by Maingroup,Member;

aggr?

Marc_Kaiser — Fri, 25 Mar 2011 23:35:17 GMT

Thank you all!

After reading and implementing all of your input, I realized I wasn't accurate enough.

Attached the app / expression with the solution I was looking for. AGGR was the solution 🙂

Marc