0 Replies Latest reply: Oct 13, 2017 1:28 PM by Jodi Hudson RSS

    Sum Problems

    Jodi Hudson

      All,

       

      I've created a report where I've assigned a value to 1,0,or .5 based on when a store opened and closed (1-store was opened the whole month, 0 - store posted activity after termination, .5 - store opened or closed that month.  Prorate column below).

       

      My intent is to be able to remove the months and sum the values by year, but I get an error putting a sum() around my prorate formula.  I don't have access to the back end, so I can't create a variable.  Here's the initial prorate formula.  How can I get it to sum?

       

      If(Sum(GROSS_ACTS_REACTS_UPGRADES)>0,

      If(isnull(DOOR_TERMINATED_DATE) and MonthStart(Date(MonthYear))>MonthEnd(DOOR_ACTIVE_DATE),1,

      if(isnull(DOOR_TERMINATED_DATE) and Month(DOOR_ACTIVE_DATE) & '/' & year(DOOR_ACTIVE_DATE)=Month(MonthYear) & '/' & Year(MonthYear),.5,

      if(not(isnull(DOOR_TERMINATED_DATE)) and Month(DOOR_TERMINATED_DATE) & '/' & year(DOOR_TERMINATED_DATE)=Month(MonthYear) & '/' & Year(MonthYear), .5,

      if(not(isnull(DOOR_TERMINATED_DATE)) and MonthStart(MonthYear)>MonthEnd(DOOR_TERMINATED_DATE),0,1)))),0)

       

      Jodi