Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Conditional sum assigned to value in field


I have the following problem: I have a table with the following fields: CaseID, CaseOpenDate, CaseCloseDate.

What I want to have a daily status of how many cases are currently open. For doing so, I'd have to assign a conditional sum assigned to each CaseCloseDate, in order to get the the number of cases that were open at that time. I guess I'd have to sum the cases with an open date lower or equal than the CaseClosedDate and then substract the cases with a closed date lower or equal the CaseClosedDate. I would want to have this value for every  CaseCloseDate. I though about making a loop, but I'm not sure which condition I should set the loop to.

For better understanding, this is what I have at the moment (in a simplified way):

Case IDCaseOpenDateCaseCloseDate

So, what I'd want is to get the following:


Can you please help me?

Best regards,

Sergio Peschiera

Tags (1)
1 Solution
3 Replies
Not applicable

Re: Conditional sum assigned to value in field

You could try it within the gui with an expression like this:

sum(aggr(count({< Date = {">=$(=only(CaseOpenDate))"}, Date = {"<$(=only(CaseCloseDate))"}>} [Case ID]), [Case ID]))

whereat it counts against a independ date-field which is also the dimension-field.

Alternatively is an interval match within the script - see here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

- Marcus

Not applicable

Re: Conditional sum assigned to value in field

Hi Marcus,

Thanks a lot for your answer. I now see that I forgot to mention I wanted to do the linkage in the script. I saw the link you've added but it seems to work only for predefined intervals. I'd want an interval between the min date and the date of reference; hence having one interval per date in the data. Do you know if there is any way I can apply the intervalmatch with intervals depending on the link's value?

Thanks again for your help!

Community Browser