Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!