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.
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))
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))
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.
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;
