Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Conditional sum assigned to value in field


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 IDCaseOpenDateCaseCloseDate
3458945403/08/201404/08/2014
4748754703/08/201405/08/2014
4753602404/08/201407/08/2014
4792354805/08/201406/08/2014
4715740905/08/201406/08/2014
4849057406/08/201406/08/2014
4910954306/08/201407/08/2014

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

CaseCloseDateOpenCases
04/08/20142
05/08/20143
06/08/20142
07/08/20140

Can you please help me?

Best regards,


Sergio Peschiera

Tags (1)
1 Solution
3 Replies
marcus_sommer
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