Tracking Differential Growth for Nth Week

[bcolburn]

Hello all,

I'm working on a research study and am trying to track enrollment over the course of the study, both by week and by month. My current model for tracking summary fields has worked fine for more contained variables with fewer outcomes, but this one demonstrates the naivete of my model.

MY GOAL: Track the number of participants that enroll each week (or month) over time and plot a bar graph of enrollment per time period over time.

MY METHOD TO DATE (I'm new): I have assigned each record an integer to code for the "study week" in which they were enrolled. That is to say, if they were enrolled in the first week of the study, the variable "ASSIGNStudyWeek" would be 1 and so on. I already have a great way of designating that via a single variable. However, I have not yet come across a good way to count the number of like variable responses since all of the variables in the database (which I inherited, and did not design) use a single value list with multiple values. That is to say, instead of creating a separate variable for "male" and "female", there is a single variable "sex", from which a record can either be marked "male" or "female". Because a summary field that counts "sex" cannot distinguish between these answers (that I know of...), I create a new field (MARK) for each value to indicate if it was selected. So MARKMale uses a calculated if statement that returns 1 if it was selected and 0 if not. I can then use a summary field (COUNTMale) to get a count of how many records store that value and use the result for further analysis using getsummary() functions in related calculations.

THE ISSUE: This study will have 300 study weeks before it ends. So this means for my previous method to work, I would need to create a MARK and COUNT field for EVERY SINGLE study week. This seems preposterous and as I said, demonstrates my naivete as a programmer. I would like to design a partner variable for "ASSIGNStudyWeek" that can recognize every possible outcome (1-300) and count the number of records for each value, which would then be a count of newly enrolled participants for that week.

IT GETS BETTER: I also need to create a way of tracking GROWTH, and not just total study enrollment. Therefore, it seems like I need to create a calculation this:

[ "GROWTHWeek" = GetSummary ( COUNTStudyWeek ( N ) ; Study Week ) - GetSummary ( COUNTStudyWeek (N-1) ; Study Week ) ]

The problem is, I don't know how to address the "N" in this equation. I don't know if FM has a way to reference variables in this way. And if so, I'm not sure if "N-1" will actually do what I want, since the calculation is built into the actual field name. So I know that this formula is impossible (highly likely). What is a better alternative? I thought about creating a second variable like ASSIGNPriorStudyWeek as described above (from 0-299), then just substitute that into the second term above.

In short, I'm in WAY over my head. I've never coded before in my life and I've only been using FileMaker for a month. So given that I've tried everything I know, I'm turning to you for help.

Sorry for the long post. I'd rather give ALL the details now so I can get an appropriate answer rather than go back and forth filling in the blanks over the course of a few weeks.

THANK YOU!!!!!

[comment]

MY GOAL: Track the number of participants that enroll each week (or month) over time and plot a bar graph of enrollment per time period over time.

OK, that seems simple enough. But what has sex to do with this?

I also need to create a way of tracking GROWTH, and not just total study enrollment.

That could be more difficult. Perhaps you should have a table of time periods and do the reporting from there.

Note: charting is a red herring here. If you can produce a report showing the correct numbers, you can also produce a chart using these numbers.

---

Is this a duplicate thread?

http://fmforums.com/...186#entry374186

Edited September 30, 2011 by comment

[bcolburn]

1.) The other thread was from a related issue, but I solved it myself. I just haven't posted the solution I came up with yet. This is a related, but different issue.

2.) he Sex variable was just an example of how I have been creating my data set. I used it to demonstrate how I would do approach this problem. To translate, in the same way I did for "male" and "female", I'm saying that my current method would have me create MARKWeekN and COUNTWeekN in order to track enrollment for each week. But unlike sex, which has just two results, this one has 300. So that means creating 600 variables to track enrollment, which seems wrong to me. Also, I'm interested - you said that graph seems simple enough, but how would I do that?

3.) My variable ASSIGNStudyWeek uses a formula to calculate the the study week of a particular record based on the start date plus a range of days elapsed until the date of record entry and a series of if statements with multiples of seven. The study month assigns values to records using GetMonth (EntryDate). I agree that your method would work well, but the thing is, the database doesn't currently understand what week the study as a whole is in - it only understands what study week a record was created in. Therein lies the problem - is there a way to fix that?

[comment]

I am afraid your answers are only confusing me more. I suggest you describe what you have (i.e. the data) and what you want to have (the type of report/chart) - and leave out what you have tried (and presumably didn't work).

Assuming you have a table of Enrollments where each enrollment is a record with a date, and that there is an agreed method to calculate the week number of a given date, producing the report and chart as described at the beginning of your original post is rather trivial:

SubSummmaryChart.zip

Of course, the chart is not likely to be very useful if you show 300 weeks all at once...

[bcolburn]

HAHAHAHA. Oh man. You're incredbly smart and experienced and this makes me laugh so hard. That's WAY easier than what I imagined might work. But bonus points for creativity?

[bcolburn]

However, I do have a question. I ultimately want to further stratify that data (and graph) by case type. If I move that chart into a sub-summary part that sorts by case type and then just format the chart with an OnObjectEnter script-trigger to run a script that further sorts by study week, will that work? If not, what is a better way of doing this? I ultimately want that chart to appear in a sub-summary report for each case type.

[comment]

I ultimately want to further stratify that data (and graph) by case type.

Move the chart to a sub-summary by type part. Make sure your records are sorted by type and by week.

format the chart with an OnObjectEnter script-trigger to run a script that further sorts by study week

I am afraid that's gibberish to me.

[bcolburn]

Nevermind. I think I know what I need to. Didn't order any sass on the help request though. Thanks for your assistance.

[comment]

Didn't order any sass on the help request though.

???

[bcolburn]

Only one more question. My charts are only showing bars for weeks with counts greater than 1. I want it to show every week, even if the count was zero. Do you have any idea why this is happening?

[comment]

My charts are only showing bars for weeks with counts greater than 1

A chart - just like a report - cannot show data that doesn't exist. If you want to include weeks with no enrollments, you will need to use a "weeks" table and pre-populate it with the week numbers you wish to include in your report.

[bcolburn]

I'm not sure I understand. Can you clarify how I would use that table?

[comment]

Here's a simplified example:

Chart2.zip

[bcolburn]

So I think I understand what you did, but unfortunately, when I draw from my new table occurrence, none of my records of interest populate the layout. Plus, the serial number doesn't seem to be actually showing up, since it is assigned on creation and my records have all already been created. Is it possible to just create a new variable in the table I'm already using for study week that using my start date and the let () function?

[comment]

I think I understand what you did

I am only guessing from the rest of your reply - but my guess is that you don't.

[bcolburn]

Well as I've already said, I'm new to this and am working with a steep learning curve. So would you like to acknowledge that and work with me to actually HELP me understand or just leave unflattering passive-aggressive comments that don't accomplish anything?

[Vaughan]

I don't think Comment was trying to be unflattering.

[bcolburn]

Alright. Let's start over. I don't understand how you made your serial number field for the week. Can you explain it to me please?

[comment]

Can you explain it to me please?

Sorry, I can't. You have the file in front of you - I suggest you spend the time to take it apart and see how it ticks. This is not meant to be unflattering or passive or aggressive or whatever. It's just that I don't understand what's not clear to you.

---

BTW, it is my experience that this is really the best way to learn.

[bcolburn]

I finally got around to examining this and it makes a lot of sense. My only question is - is there any way to place this chart on a different layout that draws from a separate table? When I put it on a layout drawing from patient records, it shows a bar for each record with data rather than a single par week.