0 Replies Latest reply: Mar 10, 2011 12:35 PM by Naomi James RSS

    Maximum value of an aggregate sum

    Naomi James

      I have a formula written that is looking at the aggregate of the "sold value" for each partner rep. I then want to take the maximum value based on another formula. I have this formula working when I look at it YTD, but when I try to limit it to the current month it no longer works.

       

       

      =

      max({1}aggr(sum({1<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>} if(inmonth([Closed Date],zToday,0),[Calculated Sold Value SRP] )), [Partner Rep]),(floor( count({1<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>} distinct(if(inmonth([Closed Date],zToday,0), [Partner Rep])))*.1)))

      So, the formula that determines the number I want returns 30 (which is correct) when I stick only that formula into a text field. But, when I put it into the formula to indicate which max value I want, it doesn't work. I am getting the top value each time, rather than the 30th value.

      I am guessing that I have one little error in this that I am overlooking. Any help would be greatly appreciated.

      Thanks