Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

anthonyeeles
New Contributor II

Cases falling in a certain date range

Hello

Our database stores a history of our cases and what stages they passed through, with start and end dts for each.

So the SQL table has CaseID, Step, StartDt, EndDt.

Each case will have a number of rows (between 1 and 7) each with a different Step. 

The difference between startdt and enddt may be less than one month or many months, depending on the case.

I need to be able to work out, for the first day of each month, how many cases (e..g distinct caseIDs) were "in" each step (i.e the start of the month was >= startdt and < enddt.

Any suggestions? I've tried various combinations of set analysis and if statements, alongside a generic calendar with month dates in, but can't get what I'm looking for.

Thanks

Anthony

1 Reply
venkatg6759
Contributor III

Re: Cases falling in a certain date range

=Count({<StartDt={">=$(=StartDt)" ,EndDt={"<=$(=EndDt)"}Distinct caseID)

Community Browser