Created vs Closed records over time

[stevejones1]

Hi

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!

[juraj_misina · Accepted solution]

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