formula to select combination of fileds

[jp246]

I need a foumula to pull this info:
report is neede for date of service_date - eg July 1 - 31, 2006

I need to have a count of care_type - palliative, long term, short term, and not assessed, but based on the assessment_date/

Here are some examples of the entries:

assessment_date care_type
July 15, 2006 long term - would be long term

example 2

July 15, 2006 long term
July 1, 2006 short term
if care type changes during the month of service date, then in this case the patient would be considered short term. the exception to this is if the patient became palliative, then palliative takes presedence.
example 3

August 2003 long term
this example would be long term

Thanks for your help, I am not a programmer, but an info analyst - I appreciate your help!!

 

[synapsevampire]

You show short term starting PRIOR to long term, yet it's considered short term?

The rules aren't very clear, and is this for specific clients, or some other grouping?

-k

 

[jp246]

sorry, first time at this. I am pulling the data out by service_ date ie July 1 - July 31, 2006. There are 2 fileds that need to be taken into consideration: assessment_ date and care_type

The last entry of assessment_date is the one that is pulled first as well as the last entry of care_type UNLESS:

-the assessment_date can occur at any time but if it changes during the month of July 2006 then the following rules must be applied:

(there are four types of care that the nurse assesses the client of: palliative, long term, short term or unassigned, and she enters these assessments herself in the database)

If the care_type changes to palliative, then the care_type is palliative, no matter when the assessment date is.

If the care_type changes during the month of service, so in this case, in July, then the first care_type entered is the one classified to the client for that month. ie if the client was reassessed on July 15 and became a long term client, and was previously short term, the client is still considered short term for the month of July.

hope this is clearer?

 

[lbass]

Assuming you have a record selection formula that limits the data to a specific month, you could insert a group on {table.client} and then create a formula {@palliative}:

if {table.caretype} = "Palliative" then 1

then go to report->selection formula->GROUP and enter:

(
sum({@palliative},{table.client}) > 0 and
{table.caretype} = "Palliative"
) or
(
distinctcount({table.care_type},{table.client}) > 1 and
{table.assessmentdate} = minimum({table.assessmentdate},{table.client})
) or
(
distinctcount({table.care_type},{table.client}) = 1 and
{table.assessmentdate} = maximum({table.assessmentdate},{table.client}
)

Then insert 4 running totals, which are a distinctcount of {table.client}, evaluate using a formula:

{table.caretype} = "Palliative" //or "Long Term", etc.

Reset never. Display the running totals in the report footer.

-LB

 

[jp246]

Thanks for your reply. I have encountered a problem though, sorry if I have missed something!
I do have a selection to a specific month. I created a group homecare_event.client.id and created a formula @{palliative}

Next, I chose select expert, Group section, formula and then entered the formula below and I am receiving this message:
This function cannot be used because it must be evaluated later
(
sum({@palliative},{homecare_event.client_id})>0 and
{assessment.care_type} = "palliative"
) or
(
distinctcount({assessment.care_type},{homecare_event.client_id}) > 1 and
{assessment.assessment_date} = minimum({assessment.assessment_date},{homecare_event.client_id})
) or
(
distinctcount({assessment.care_type},{homecare_event.client_id}) = 1 and
{assessment.assessment_date} = maximum({assessment.assessment_date},{homecare_event.client_id})
)

 

[lbass]

Sounds like you have this in the record selection area. Make sure you are entering it at report->selection formula->GROUP. There is no reason you would get this message if you placed the above in the right section--unless you didn't create {@palliative} as instructed.

-LB