topic Report based on diff dates in QlikView

Report based on diff dates

parpatra — Fri, 29 Oct 2010 19:55:05 GMT

Hi All,

I have a data in this format:

App No	App date	Closed	Closed date
1	Jan	Yes	Jan
2	Jan	Yes	Feb
3	Jan	No
4	Feb	Yes	Feb
5	Feb	Yes	Mar
6	Feb	No
7	Mar	Yes	Mar
8	Mar	Yes	Apr
9	Mar	No
10	Apr	Yes	Apr
11	Apr	Yes	May
12	Apr	No
13	May	Yes	May
14	May	Yes	Jun
15	May	No
16	Jun	Yes	Jun
17	Jun	Yes	Jun
18	Jun	No

I need to show a ratio between closed/total number of applications grouped by month.

So for Jun, ratio=count(all apps which have closeddate in Jun)/[Count(all app which have app date in june)+count( all apps which are closeddate in june and have appdate in previous months)]

This evaluates to 3/[3+1]=3/4=0.75

I am facing a problem to create a list box showing month, which I intend to create from the Appdate. The expression refers both the closed date and appdate,I am not able to make sure which one to use as my dimension in a chart to show the ratio across months.

Report based on diff dates

Anonymous — Fri, 29 Oct 2010 20:40:12 GMT

You can create list of months as data island, and use it as dimension in your chart. Expression will be using conditions, like (more or less):
count (if(appdate=Month, AppNo)) or count (if(closedate=Month and closedate> appdate, AppNo))

Report based on diff dates

parpatra — Fri, 29 Oct 2010 20:51:41 GMT

Thanks Michael...

I am pretty new with QV and not aware of 'data island'...can you please help me with an example.

Is it like a dummy table with all the date information?

Report based on diff dates

johnw — Fri, 29 Oct 2010 21:09:54 GMT

You could merge your two dates into one date field, distinguished by activity. The Closed field would be on a separate table to avoid duplication, or simply calculated on the fly from the existance of a closed date.

App No, Closed
1, Yes
2, Yes
3, No
etc.

App No, Activity, Date
1, Applied, Jan
1, Closed, Jan
2, Applied, Jan
2, Closed, Feb
3, Applied, Jan
etc.

And if I understand correctly:

ratio = count({<Activity={'Closed'}>} distinct "App No")/count(distinct "App No")

Edit: Renamed field "Date Type" to "Activity", which I think better describes the contents of the field.

Report based on diff dates

Anonymous — Fri, 29 Oct 2010 21:11:36 GMT

It's a table not linked with any other table in the data model. In your case it will be one field, Month, with all Month values you may need. You can create it, for example, like this, if the source fields contain correct values, so get only values you need and nothing more than that:


MonthDataIsland:
LOAD DISTINCT
 "App date" as Month
RESIDENT DataTable;
CONCATENATE LOAD DISTINCT
 "Closed date" as Month
RESIDENT DataTable;

Report based on diff dates

parpatra — Fri, 29 Oct 2010 21:35:17 GMT

Thanks fro your suggestions...

I will try these options and get back to you if needed...