topic Re: Re: AGGR in Set Analysis in QlikView

AGGR in Set Analysis

Tue, 30 Sep 2014 17:04:43 GMT

I am trying to sum the top 10 series within a television network. I was able to successfully get a list of the top 10 series using a calculated dimension of =aggr(if(rank(sum(households))<=$(=vYLimitNum),$(=vYAxis)),$(=vXAxis),$(=vYAxis))

vYLimitNum=10

vYAxis = Series

vXAxis = Network

Now when I go to try and get a sum of the households for just those series aggregated to the network I can't get my sum function to aggregate correctly. A simple sum(households) returns all HHs correctly. I've tried this sum expressions...

sum({<Network=, Series={"=aggr(if(rank(sum(households))<=$(=vYLimitNum),$(=vYAxis)),$(=vYAxis))"}>} households)

But it returns no data.

Thoughts on how I might be about to correctly setup that set analysis? Of if I have to use set analysis. This has to be dynamic so I can't create a summary table.

Re: AGGR in Set Analysis

JonnyPoole — Tue, 30 Sep 2014 17:08:46 GMT

how about wrapping with sum() ? Otherwise i'd like to see the QVW if you can share.

sum(

aggr(if(rank(sum(households))<=$(=vYLimitNum),$(=vYAxis)),$(=vXAxis),$(=vYAxis))

)

Re: Re: AGGR in Set Analysis

Tue, 30 Sep 2014 17:46:05 GMT

I am not able to share the full document but I can share the attached. The chart labeled "By Network Top Series HH" is the one that will not return for me. The chart "Top 10 Series" is accurately showing the right Series and HH values. I want to aggregate up that table into just the Network level.

Re: AGGR in Set Analysis

Tue, 30 Sep 2014 17:59:16 GMT

HA! I figured it out and that SUM suggestion did work I just had to tweak the aggr to return my metric and not the dimension.

I ended up with this expression:

sum(aggr(if(rank(sum(Households))<=$(=vYLimitNum),Households),$(=vXAxis),$(=vYAxis)))

Thanks!

Re: AGGR in Set Analysis

JonnyPoole — Tue, 30 Sep 2014 19:13:28 GMT

Very good. Don't forget to close out the thread .:)