Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jhudson1
New Contributor

Sum Problems

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

Tags (1)
Community Browser