Count active cases between two dates

[benst12]

I am attempting to make a line chart that will account for all active cases by month. My data has the form of 

CaseID	Start	End
1	1/1/2020	1/1/2021
2	2/1/2020	2/1/2021
3	3/1/2020	6/1/2020
4	3/5/2020	3/5/2021
5	3/10/2020	4/12/2020
6	4/7/2020	4/7/2021
7	5/1/2020	5/1/2021
8	6/17/2020	7/1/2020
9	7/7/2020	7/7/2020

And the optimal output would look something like

Date	Count
1/31/2020	1
2/28/2020	2
3/31/2020	4
4/30/2020	5
5/31/2020	6
6/30/2020	6
7/31/2020	7

I have tried building a canonical calendar and that does not quite get the job done. Does anyone have any ideas on set analysis or a change to the data load that would allow this type of calculation?

[chrismarlow]

Hi,

You can do a horrible hack using ValueList (that might work if say you were only showing 1 year at a time, otherwise your list is going to get very long);

ValueList('31/01/2020','28/02/2020','31/03/2020','30/04/2020','31/05/2020','30/06/2020','31/07/2020')

Sum(If(ValueList('31/01/2020','28/02/2020','31/03/2020','30/04/2020','31/05/2020','30/06/2020','31/07/2020')>=Start AND NOT ValueList('31/01/2020','28/02/2020','31/03/2020','30/04/2020','31/05/2020','30/06/2020','31/07/2020')>End,1,0))

(I'm on UK dates ...)

In script I don't think you will be able to get away from the fact you will need to create a many to many table that maps each case to each month that it matches, either through looping/joining  or possibly an interval match (but you might need to loop/join to get your ranges first, or they might drop out of your calendar table).

Cheers,

Chris.

[Kushal_Chawda]

Data:
LOAD CaseID, 
     date(Start+IterNo()-1) as Date, 
     End
FROM
[https://community.qlik.com/t5/Qlik-Sense-App-Development/Count-active-cases-between-two-dates/td-p/1731131]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1)
while Start+IterNo()-1<=End and Start+IterNo()-1<=Today();

MonthEnd:
LOAD FieldValue('Date',RecNo()) as Date,
     monthend(FieldValue('Date',RecNo())) as MonthEnd
AutoGenerate FieldValueCount('Date');

Then you can create chart with dimension MonthEnd & expression count(distinct CaseID)

[benst12]

I pretty much already use a similar method when I create the data file before it is read in to Qlik, but the issue when doing this is the data file becomes too large and causes performance issues, so I'm pretty sure switching the looping from outside to inside Qlik would cause issues as well. Do you know any front end solutions with Ifs or set analysis?

[Kushal_Chawda]

Slightly different approach via script. See if helps. 

Data:
LOAD CaseID, 
     Start, 
     End
FROM
[Data.qvd];

let vStartDateField ='Start'; // define field name for start date

let vEndDateField ='End'; // define field name for end date

Dates:
LOAD FieldValue('$(vStartDateField)',RecNo()) as Dates
AutoGenerate FieldValueCount('$(vStartDateField)');

LOAD FieldValue('$(vEndDateField)',RecNo()) as Dates
AutoGenerate FieldValueCount('$(vEndDateField)');

MonthEnd:
LOAD floor(MonthEnd(Dates)) as MonthEnd
Resident Dates
where Dates<=Today();

DROP Table Dates;

for i=1 to FieldValueCount('MonthEnd')

Month:
LOAD 'if('&FieldValue('MonthEnd',$(i))&'>=floor($(vStartDateField)) and ' &FieldValue('MonthEnd',$(i))&'<=floor($(vEndDateField)),1,0) as _' &FieldValue('MonthEnd',$(i))&'_' as Condition,
      '_'&FieldValue('MonthEnd',$(i))&'_' as FieldName
AutoGenerate 1;

NEXT

T1:
NoConcatenate
LOAD Concat(Condition,','&chr(10)) as Condition,
     Concat(FieldName,','&chr(10)) as FieldName
Resident Month;

DROP Tables MonthEnd,Month;

let vCondition = Peek('Condition',0,'T1');

let vFieldName = Peek('FieldName',0,'T1');

DROP Table T1;

T2:
NoConcatenate
LOAD *,
     $(vCondition)
Resident Data;

DROP Table Data;

T3:
CrossTable(MonthEnd,Value)
LOAD CaseID,
     $(vFieldName)
Resident T2;

T4:
NoConcatenate
LOAD CaseID,
    date(TextBetween(MonthEnd,'_','_')) as MonthEnd,
    Value
Resident T3;

DROP Table T3;

DROP Fields $(vFieldName);

You can now create chart with Dimension MonthEnd and expression Sum(Value)

[jcdatasax]

First create a new table with resident load to get a snapshot of each record active during the month.   I learned this from @hic !

Load *,

Date(Addmonths(Monthstart(Start), + Iter()-1) as StartMonth

Resident original_table

While 

Addmonths(Monthstart(Start), Iter()-1) <= Monthend(End);