Created vs Closed records over time

stevejones1 — Wed, 25 Nov 2020 16:16:04 GMT

Can someone please confirm if i am going in the right path , i need to create a chart for Created vs Closed cases.

i have concatenated the table by reading it twice for closed and created.

then while counting the closed cases using case status.

say for example i am selecting case # 42465221 .. which is created in July and closed in feb .. why do i see the count for closed cases as 1 even though i select July from date filter.

Thanks!

Re: Created vs Closed records over time

juraj_misina — Thu, 22 Feb 2018 21:26:37 GMT

Hi Steve,

you do not need to load your cases twice. Instead create a "date bridge" table for open date and close date for example like this:

DateBridge:

LOAD

CaseOpenDate as Date,

'Open' as DateType

Resident Case;

Cocatenate(DateBridge)

LOAD

CaseCloseDate as Date,

'Close' as DateType

Resident Case;

Attach your calendar table with the DateBridge table (not Case table) and then you can use following expressions:

=count({<DateType= {'Close'}>} DISTINCT(CaseNumber)) //for cases closed in a particular period
=count({<DateType= {'Open'}>} DISTINCT(CaseNumber)) //for cases opened in a particular period

Hope this helps.

Juraj

topic Created vs Closed records over time in QlikView

Created vs Closed records over time

Re: Created vs Closed records over time