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