3 Replies Latest reply: Dec 2, 2013 11:15 AM by Jen Starr RSS

    AGGR is only working when selection of Market and State fields are selected

    Jen Starr

      This formula works only if I select a Market and a State, however if I look at all States within a Market, then this column is blank. 

       

      sum(if(aggr(rank((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2),Market,State,ACC_ID)

      >aggr(count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID),Market, State, ACC_ID)/2,

      aggr(((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>} RB_Amount)*1.03)*.2),Market, State, ACC_ID)))

       

      Any help would be greatly appreciated. 

       

      Thanks,

      Jen

        • Re: AGGR is only working when selection of Market and State fields are selected
          Stefan Wühl

          What if you use only 1 aggr() function:

           

          sum(

          aggr(

          if( rank((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2)

          > count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID) /2,

          ((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>} RB_Amount)*1.03)*.2)

          ,Market,State,ACC_ID)

          )

            • Re: AGGR is only working when selection of Market and State fields are selected
              Jen Starr

              Thank you for your reply! 

               

              If I only use one aggr() then the >count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID) /2, doesn't work properly.  What I'm trying to accomplish is this:

               

              For projections, half of all accounts need to be upgraded by market and by state. I need to show two columns one with the minimum upgrade dollars and one with the maximum upgrade dollars.  I did this by ranking the accounts, then if the rank is greater then half the number of accounts, by market by state, then I do the upgrade calculation for the minimum upgrade dollars.  I do the same for the maximum upgrade dollars, but the rank is less than or equal to half the accounts.  

                • Re: AGGR is only working when selection of Market and State fields are selected
                  Jen Starr

                  Thank you for your help swuehl.  I was able to make a change to your original suggestions to make it work.  The change I made is in Bold.

                   

                  Thank you again!

                   

                  Sum(

                  aggr(

                  if( rank((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >}  RB_Amount)*1.03)*.2)

                  > count({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} Total  <Market,State> ACC_ID) /2,

                  ((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2))

                  ,Market,State,ACC_ID)

                  )