Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Thanks...I tried your formula and I get no values per row, but I do get a total...
So your desired result should be this?
BOOKSUBJECT Count
A 1
B 1
C 1
D 1
Yes, as if you take just the minimum year for each BOOKSUBJECT, you get only one BOOKNAME per BOOKSUBJECT with this small dataset
What is your Dimension ?
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))
My dimension is BOOKSUBJECT
I'm trying to perform a count(BOOKNAME) as my expression (?)
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()