Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
New Contributor

Sum Problems


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)


Tags (1)