summarising a distinct count field 2

[gromboy]

Hi,
I have a database which has patient episode information. Each patient will recieve a Cycle of treatment. Within the cycle can be any number of treatment days. For example Cycle 1 may have treatment days 1 and 8. And when they go onto Cycle 2 they may have treatment days 1, 8 and 15. Just depends how the Cycles of treatment are defined.

I'm trying to get a figure for the number of treatment days the patient has attended for a given period. For example in the above senario they will have attended for 5 treatment days in total. 2 days in Cycle 1 and 3 days in Cycle 2.

In the database there is a row for every drug given on a particular treatment day. The number of rows therefore cannot be counted. I did a distinct count on the treatment days but where a there are 2 x "Day 1" treatment days it obviously counts this as 1, and not 2. If I do a standard count I get all the rows for that treatment day which is incorrect.

I have grouped the report and dropped a distinct count field onto the report, then exported into EXCEL and used the sum function to give a value, but hey not very clever...

For some reason I cannnot find out how to do this...
Any ideas ? much appreciated as ever !

Regards
Steve

 

[lbass]

What fields are you working with? Do you have cycle ID fields that are unique? You could potentially concatenate a cycleID and treatment day in a formula and then insert a distinctcount on that.

-LB

 

[gromboy]

Hi,
There isnt a cycle id field. I have appointment date, patients number, cycle number, cycle day, also loads of other field, but nothing unique to the cycle...
Steve

 

[lbass]

How do you know what "goes with" the cycle? Is the cycle number unique? Please show some sample data (multiple rows) that illustrates the problem.

-LB

 

[gromboy]

I've attached rows of data for you to look at.
There are more fields but not sure if they're relevant

 

[lbass]

From what I can see, all you need to do is concatenate the cycle number and the day number. These look like they are of number datatype, so try creating this formula in the field explorer:

totext({table.cyclenumber},0,"")+totext({table.daynumber})

Place this in the detail section of the report and insert a distinctcount on it at the patient group level (I didn't see a patient field, there but I assume there is one, since you want to evaluate this per patient).

-LB

 

[gromboy]

LB ... your a star.. works a treat
Thanks soooo much
Steve

 

[gromboy]

I would have thought it would also work for month. My grouping is appt date by month, then patient number But the figure for month isnt correct. I really need the total of days per month as well as split by patient.
It works fine at pt level...
Any ideas why ?
Steve

 

[lbass]

The issue would be that multiple patients might have the same cycleno-dayno, I guess? So that the month count is an undercount? You could either concatenate the patient ID with the cycleno-dayno formula or you could use a variable to collect the results for each patient and display in the month group. Which way do you want to go?

-LB

 

[gromboy]

Not sure...
As long as I get the right answer...
The variable might be better..Can you explain both ?
Steve

 

[lbass]

I would try the concatenation first, as it is simpler:

totext({table.patientID},0,"")+" "+totext({table.cyclenumber},0,"")+" "+totext({table.daynumber},0,"")

I also added in spaces to distinguish the numbers.

-LB

 

[gromboy]

I'm getting an error
"Too many arguements have been given to this function"

at this point in the formula below

0,

 

[lbass]

Then the field that you have wrapped in totext() is already a string, so just add the field itself instead of the totext(field,0,"").

-LB

 

[gromboy]

({PATDRUG.DISTRICTNO},0,"")+" "+totext({PATDRUG.CYCLE},0,"")+" "+totext({PATDRUG.CYCLE},0,"")

Now getting an error, highlighting the first comma after {PATDRUG.DISTRICTNO},
Says "The ) is missing

 

[lbass]

{PATDRUG.DISTRICTNO}+" "+totext({PATDRUG.CYCLE},0,"")+" "+totext({PATDRUG.CYCLE},0,"")

-LB

 

[gromboy]

hhmmmm dosent seem to give the right figure...
The formula calculated 66, the actual count was 85... for the first month..

Something's not quite right....

 

[lbass]

Actually, there is a typo in your formula, which I copied. should be:

{PATDRUG.DISTRICTNO}+" "+totext({PATDRUG.CYCLE},0,"")+" "+totext({PATDRUG.[red]daynumber[/red]},0,"")

Not sure it's in the same table.

-LB

 

[gromboy]

ah yes... I should have noticed that...

Works a treat now... thanks again... have a good weekend
Steve