topic Re: compare counts by 2 different dates in QlikView

compare counts by 2 different dates

Thu, 15 Jan 2015 02:29:20 GMT

Hi I'm am trying to calculate the closure rate for cases by month.

An example of the data is as follows:

Case ID	Created Date	Closed Date
123	1/7/2014	12/7/2014
234	5/7/2014	6/8/2014
345	13/8/2014	15/8/2014
456	15/8/2014	28/8/2014

The results I need to see are:

Year	Month	No. Cases Created	No. Cases Closed	Closure Rate
2014	Jul	2	1	50%
2014	Aug	2	3	150%

My biggest issue is the different dates. I can create a master calendar, but how can I link two different date fields from the same table to it, as they can't have the same field name?

Any help would be appreciated.

Re: compare counts by 2 different dates

Thu, 15 Jan 2015 02:49:18 GMT

For this case you must have two different calendars one for [Created Date] and other for [Closed Date] . You can have multiple calendars if you have multiple dates and you will need to apply selections or create comparative (rolling periods) analysis. Just name the second one with a different names not only for the table itself but also for the fields, thus:

CalCreateDate

MonthCreateD

MonthSerialCreateD

YearCreateD

CalClosedDate

MonthClosedD

MonthSerialClosedD

YearClosedD

and so on.

Re: compare counts by 2 different dates

jonathandienst — Thu, 15 Jan 2015 06:03:31 GMT

Have a look here:

Canonical Date

Re: compare counts by 2 different dates

Tue, 20 Jan 2015 21:43:33 GMT

Thanks. fixed it completely!