aggr function inside set analysis not working

[#1]

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?

[simondachstr]

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.

[#3]

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

[simondachstr]

So your desired result should be this?

BOOKSUBJECT  Count

A                         1

B                         1

C                         1

D                         1

[#5]

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

[#6]

What is your Dimension ?

[simondachstr]

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

[#8]

My dimension is BOOKSUBJECT

[#9]

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

[simondachstr]

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