Hello.
The problem I have relates to fixing the dimension on a chart for all the months of the financial year (April 10 -March 11). In the scenario I want to count ALL the clients that were considered to be in Effective Treatment in that month. The issue is that the x-axis is controlled by a table that explicitly states the financial Month/Year and is linked to the main data table on the field Assessement_MonthStart.
The data provided is based on a client ID, the date they were assessed (Date_Assessed) and the date they were discharged (DISCHARGE). A flag, Client_Effective_Treatment, has a 1 if treatment considered successful and a 2 if not successful.
When running the expression =count(distinct If(Client_Effective_Treatment=1,Client_ID)) for any given month it (naturally!) counts only those records where the field Assessement_MonthStart = that month e.g. Apr 10 = count of 3.
What I really want is to count all those in successful treatment (Client_Effective_Treatment=1) in that month, not just those assessed in that month. A client could have been assessed the months before (e.g. Feb 10) and is still actively a client in effective treatment in April. Only when a client has been discharged then in the month after discharge they should not be counted. If there is no Discharge date and Client_Effective_Treatment=1 then they should be counted each month.
So in essence if I want to fix the x-axis with set months, how do you count valid records that are active in that month but is only evidenced by a Date_Assessed prior to that mont and a Discharge Date equal to or later than that month?
The tables below show the records that should be counted in each month - April 5, May=5, June=7.
Any help/suggestions will be greatly appreciated.
Many thanks Neil.
| Data that should be counted in April | | |
| | | |
| Client_Attrib | Client_Effective_Treatment | Date_Assessed | DISCHARGE |
| M-C-5/1/1970-M | 1 | 12/06/2009 | |
| M-D-19/6/1971-M | 1 | 15/01/2010 | |
| A-S-23/3/1972-M | 1 | 12/02/2010 | |
| M-K-12/3/1956_M | 1 | 12/03/2010 | 30/03/2010 |
| J-Y-12/4/1974-M | 1 | 01/04/2010 | 30/04/2010 |
| A-N-1/7/1971-M | 1 | 10/04/2010 | 30/05/2010 |
| M-C-10/1/1975-M | 1 | 18/04/2010 | |
| L-C-1/4/1972-M | 2 | 03/05/2010 | 04/06/2010 |
| K-H-8/12/1980-F | 2 | 04/05/2010 | 05/06/2010 |
| D-S-26/3/1988-M | 1 | 15/05/2010 | 16/06/2010 |
| D-M-8/12/1984-M | 1 | 04/06/2010 | 05/07/2010 |
| N-H-9/11/63-M | 1 | 22/06/2010 | 30/07/2010 |
| | | |
| Data that should be counted in May | | |
| | | |
| Client_Attrib | Client_Effective_Treatment | Date_Assessed | DISCHARGE |
| M-C-5/1/1970-M | 1 | 12/06/2009 | |
| M-D-19/6/1971-M | 1 | 15/01/2010 | |
| A-S-23/3/1972-M | 1 | 12/02/2010 | |
| M-K-12/3/1956_M | 1 | 12/03/2010 | 30/03/2010 |
| J-Y-12/4/1974-M | 1 | 01/04/2010 | 30/04/2010 |
| A-N-1/7/1971-M | 1 | 10/04/2010 | 30/05/2010 |
| M-C-10/1/1975-M | 1 | 18/04/2010 | |
| L-C-1/4/1972-M | 2 | 03/05/2010 | 04/06/2010 |
| K-H-8/12/1980-F | 2 | 04/05/2010 | 05/06/2010 |
| D-S-26/3/1988-M | 1 | 15/05/2010 | 16/06/2010 |
| D-M-8/12/1984-M | 1 | 04/06/2010 | 05/07/2010 |
| N-H-9/11/63-M | 1 | 22/06/2010 | 30/07/2010 |
| | | |
| Data that should be counted in June | | |
| | | |
| Client_Attrib | Client_Effective_Treatment | Date_Assessed | DISCHARGE |
| M-C-5/1/1970-M | 1 | 12/06/2009 | |
| M-D-19/6/1971-M | 1 | 15/01/2010 | |
| A-S-23/3/1972-M | 1 | 12/02/2010 | |
| M-K-12/3/1956_M | 1 | 12/03/2010 | 30/03/2010 |
| J-Y-12/4/1974-M | 1 | 01/04/2010 | 30/04/2010 |
| A-N-1/7/1971-M | 1 | 10/04/2010 | 30/05/2010 |
| M-C-10/1/1975-M | 1 | 18/04/2010 | |
| L-C-1/4/1972-M | 2 | 03/05/2010 | 04/06/2010 |
| K-H-8/12/1980-F | 2 | 04/05/2010 | 05/06/2010 |
| D-S-26/3/1988-M | 1 | 15/05/2010 | 16/06/2010 |
| D-M-8/12/1984-M | 1 | 04/06/2010 | 05/07/2010 |
| N-H-9/11/63-M | 1 | 22/06/2010 | 30/07/2010 |
