topic Re: Count active cases between two dates in App Development

Count active cases between two dates

benst12 — Mon, 20 Dec 2021 21:08:46 GMT

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?

Re: Count active cases between two dates

chrismarlow — Mon, 27 Jul 2020 19:56:02 GMT

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.

Re: Count active cases between two dates

Kushal_Chawda — Mon, 27 Jul 2020 21:07:07 GMT

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)

Re: Count active cases between two dates

benst12 — Tue, 28 Jul 2020 12:14:57 GMT

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?

Re: Count active cases between two dates

Kushal_Chawda — Tue, 28 Jul 2020 23:57:56 GMT

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)

Re: Count active cases between two dates

jcdatasax — Wed, 29 Jul 2020 00:34:02 GMT

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);