Conditional sum assigned to value in field

[#1]

Hello,

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 ID	CaseOpenDate	CaseCloseDate
34589454	03/08/2014	04/08/2014
47487547	03/08/2014	05/08/2014
47536024	04/08/2014	07/08/2014
47923548	05/08/2014	06/08/2014
47157409	05/08/2014	06/08/2014
48490574	06/08/2014	06/08/2014
49109543	06/08/2014	07/08/2014

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

CaseCloseDate	OpenCases
04/08/2014	2
05/08/2014	3
06/08/2014	2
07/08/2014	0

Can you please help me?

Best regards,

Sergio Peschiera

[marcus_sommer]

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

[#3]

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!

[marcus_sommer · Accepted solution]

Have also a look here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/25/creating-intervals-from-change-dates

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions

- Marcus