topic Re: aggr function inside set analysis not working in QlikView

aggr function inside set analysis not working

Thu, 20 Nov 2014 17:46:39 GMT

Hello,

I have the following dataset:

BOOKSUBJECT BOOKNAME PUBLICATIONYEAR

A Book1 2011

B Book2 2010

C Book3 2010

D Book1 2011

A Book5 2008

B Book2 2009

I’m attempting to derive the minimum PUBLICATIONYEAR year for each BOOKSUBJECT. I was able to do this as its own formula (in its own column in a straight table)

aggr(min(PUBLICATIONYEAR),BOOKSUBJECT)

However when I attempt to set that minimum PUBLICATION YEAR value into Set Analysis to count how many BOOKNAMEs have that BOOKSUBJECT only in that minimum/first year, it ignores the per row/BOOKSUBJECT value and just takes the minimum PUBLICATIONYEAR for the entire data set. Here is as close as I got….

count({<PUBLICATIONYEAR = {"=$(#=min(aggr(min(PUBLICATIONYEAR), BOOKSUBJECT)))"} >}BOOKNAME)

So for the dataset above, when I attempt to count BOOKNAMES per BOOKSUBJECT for the minimum PUBLICATIONYEAR, I get a value of 2008 for *all* BOOKSUBJECTS even though I’m trying to get 2008 for BOOKSUBJECT A, but 2009 for BOOKSUBJECT B, etc…

Any ideas?

Re: aggr function inside set analysis not working

simondachstr — Thu, 20 Nov 2014 17:58:42 GMT

the set analysis expression evaluates in a dollar expansion ONCE for the whole chart which is why you get the minimum year of the entire data set. You'll need to either use a clever if statement e.g.

if(PUBLICATIONYEAR = min(aggr(min({1} PUBLICATIONYEAR),BOOKSUBJECT)), count(BOOKNAME))

Bear in mind this is not tested..

or use a dynamic dimension or pre calculate the information in the script.

Re: aggr function inside set analysis not working

Thu, 20 Nov 2014 18:35:04 GMT

Thanks...I tried your formula and I get no values per row, but I do get a total...

Re: aggr function inside set analysis not working

simondachstr — Thu, 20 Nov 2014 20:15:08 GMT

So your desired result should be this?

BOOKSUBJECT Count

A 1

B 1

C 1

D 1

Re: aggr function inside set analysis not working

Thu, 20 Nov 2014 20:25:39 GMT

Yes, as if you take just the minimum year for each BOOKSUBJECT, you get only one BOOKNAME per BOOKSUBJECT with this small dataset

Re: aggr function inside set analysis not working

Thu, 20 Nov 2014 20:33:23 GMT

What is your Dimension ?

Re: aggr function inside set analysis not working

simondachstr — Thu, 20 Nov 2014 20:40:13 GMT

Try the below.. nodistinct seems to be the key here.. not sure if there's a more efficient way.

=sum(aggr(if(aggr(nodistinct min({1} PUBLICATIONYEAR),BOOKSUBJECT)=aggr(nodistinct min({1} PUBLICATIONYEAR),BOOKNAME),1,0),BOOKNAME,BOOKSUBJECT))

Re: aggr function inside set analysis not working

Thu, 20 Nov 2014 21:38:42 GMT

My dimension is BOOKSUBJECT

Re: aggr function inside set analysis not working

Thu, 20 Nov 2014 21:42:02 GMT

I'm trying to perform a count(BOOKNAME) as my expression (?)

Re: aggr function inside set analysis not working

simondachstr — Thu, 20 Nov 2014 21:52:07 GMT

instead of counting, I'm flagging a 1 if my condition is true and summing the 1's up - this makes sure no unwanted duplicated records are counted twice, as we're using nodistinct in aggr()

Re: aggr function inside set analysis not working

Fri, 21 Nov 2014 14:05:28 GMT

I tried this and I get mostly zeros... I figured out a 'dirty' workaround. The PUBLICATION YEARS only go back 5 years, so I was able to use nested IFs to check to see which year the the publications started for each term and then count the BOOKNAME for that year as the 'minimum'.

if(count({<PUBLICATIONYEAR = {"<=$(=min(PUBLICATIONYEAR)+1)"} >}BOOKNAME) > 0 , count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR)+1)"} >}BOOKNAME)) ,

if(count({<PUBLICATIONYEAR = {"<=$(=min(PUBLICATIONYEAR)+2)"} >}BOOKNAME) > 0 , count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR)+2)"} >}BOOKNAME)) ,

if(count({<PUBLICATIONYEAR = {"<=$(=min(PUBLICATIONYEAR)+3)"} >}BOOKNAME) > 0 , count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR)+3)"} >}BOOKNAME)) ,

if(count({<PUBLICATIONYEAR = {"<=$(=min(PUBLICATIONYEAR)+4)"} >}BOOKNAME) > 0 , count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR)+4)"} >}BOOKNAME)) ,

if(count({<PUBLICATIONYEAR = {"<=$(=min(PUBLICATIONYEAR)+5)"} >}BOOKNAME) > 0 , count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR)+5)"} >}BOOKNAME)) ,

count({<PUBLICATIONYEAR = {"<=$(#=min(PUBLICATIONYEAR))"} >}BOOKNAME)

Re: aggr function inside set analysis not working

Fri, 21 Nov 2014 20:16:06 GMT

Clearly In this case SET Analysis wont work because SET analysis work on entire table not row level. In this case the Min Year change every row.