Pulling hair out: Need a calculation field that can count number of records within a date range

[DJRoss]

(Using FM PRO 10, Windows Vista)

what I want to happen is for a field to do this:

Count (db1::advisor_key when related records are (db1::date_field >= Mindate and db1::date_field <= Week 1)

So I have two tables. One that is nothing more than a data dump from an excel file. the advisors are using a help desk service and I am measuring their usage. Each is represented by the unique advisor_key value and all of their help desk chats are listed by date. My second table is a portal to the list where I can see when they started using the service (MINDATE) and their last usage (MAXDATE), I can even show total number of records, however I need to break down the data into weekly increments to measure usage (look for trends).

So I have been tackling the problem from the perspective of starting with their mindate and trying to count the number of records within the mindate and week 1 (mindate+7), I would than do the same for records between week 1 and week 2 and so on.

Tried with If statements, Case, Tried using basic math to subtract all the values from another, nothing is working and I know this isn't that complicated. I have become blind and need some help here. Thanks.

[comment]

I don't understand your description that well - it seems you want to define a calculation field (result is Number) in the child table =

db1::date_field >= Mindate and db1::date_field <= Week 1

Then, in the parent table, use Sum ( Child::CalculationField ) to get the number of child records that meet the criteria.

[DJRoss]

the image attached is the portal. There is a field labelled Wk1a (not shown). This is a calculation field. The intent is to sort through the dates listed in the portal and count the number that fall into a range equal to or greater than the MINDATE field and less than the Week 1 field (which is also not shown in this screenshot) In this case it would be a weeks worth with the MINDATE as the starting date. The answer in the Wk1a should be 4 as there are 4 of the 15 portal records that are within this time range.

[DJRoss]

...here it is:

Data:

I have raw data in db1 where each record contains the name of the advisor(BA Names), Date (when they used helpdesk service) and which team (Team) they belong to. There is also a counter (fixed value of 1)as well as a key (using the first and last name of each advisor)

Database:

I have a portal solution where I tap into the raw data using three tabs. The first tab is a portal where I can filter based on each individual advisor. When the portal rows show helpdesk occurances, there are local fields where that data is filtered into weekly usage numbers.

example: Jane Doe between Sept 19, 2009 and Dec 10, 2009 shows 50 occurances of using help desk in the portal) The local fields range from week 1 through week 52 where each week is tied to a unique relationship starting with the first week where ba_key=ba_key and MinDate<=date and Week1>date. The first week field is a calculation field using a Count function to count ba_key in the related field using the above mentioned relationship. This helps sort through the records and only counting those that meet the relationship requirements thus giving me the week one numbers. Jane used the help desk 50 times within the total time frame, but in her first week she may have used it 8 times. This works great in giving me that number. In order for it to work however I have made a unique relationship for each week field. That makes 52 relationships.

Now working with the second tab where these numbers are looked at on a per team level, I have to repeat the process creating 52 more and another 52 for weekly numbers at the agency level.

While it works well, I can't help but believe there is a more straightforward way to accomplish this. now I will be attaching the weekly data to chart functions, but until I get it all complete I am left wondering about a more efficient way to do this.

Thanks for any help.

[comment]

I would create another table of Weeks, with 52 (or perhaps 54?) permanent records. You need a global gAdvisorID field in this table, to be populated by the currently viewed advisor's unique key. Relate Weeks to your Data table by matching AdvisorID AND Week. The calculation fields aggregating the raw data should also be in the Weeks table.

From the viewer table, you can watch any week (or several weeks in a portal) through another relationship.

[DJRoss]

Feel free to fiddle around with this. Seeing is understanding what I am trying to do.

BAHUS.zip

[comment]

Seeing is understanding what I am trying to do.

Not really... However, I am under the impression you'll be only viewing a week at a time. If so, perhaps it can be much simpler - see the attached.

BTW, I don't know how much control you have over the raw data, but relying on the advisors' names is really not a good idea.

View.fp7.zip

[DJRoss]

The raw data field is scrubbed every other day or so, so the records are deleted and import occurs off of an excel file. Under the portal window there is a grid featuring all 52 weeks. Each with its own field. When I select the advisors name in the drop down list, their records show up in the portal, and each field on the 52 week grid will if applicable show up with a calculation showing how many of those portal records occured in week 1, week 2 and so on.

BTW, I use a counter=1 for all fields

Also I will be viewing the individual or the team or the entire workforce all 52 weeks at the same time. So it is not an issue where I select the week and get a list of helpdesk occurances, but an issue where I select the advisor or the team and get a simple record count of records in each week.

Edited December 10, 2009 by Guest

[comment]

Tthere is a grid featuring all 52 weeks. Each with its own field. When I select the advisors name in the drop down list, their records show up in the portal, and each field on the 52 week grid will if applicable show up with a calculation showing how many of those portal records occured in week 1, week 2 and so on.

Well, then you need to go back to my first suggestion, where the grid would be a portal to the Weeks table.

[DJRoss]

Here is my challenge:

I have two fields Help_week_one and Help_week_two. They use the same relationship to a root file ba_help. the ba_help file contains records of incidences of help each advisor received. So John Smith may have 10 records where he received help. Each record also contains the date when the advisor received help.

I am looking for a way to automatically show in Help_week_one and Help_week_two the number of incidences of help based on selecting advisor. So if I using a drop down menu and a key field select John Smith, both Help fields will automatically populate with data showing how many times John used Help that week. So if four of the ten records in ba_help were from Dec 28th to Jan 2 (week 1) selecting John Smith would show the number 4 in Help_week_one field and so on.

[comment]

How is this different from your previous thread:

http://fmforums.com/forum/showtopic.php?tid/212462/

or the one before that:

http://fmforums.com/forum/showtopic.php?tid/212319/

[DJRoss]

well I am trying to explain things in a way that makes it understood what I am trying to accomplish. I am not trying to be a pain here, but it seems that when I explain my problem everyone is trying to help me from a perspective of using dates as a searchable function to sort the data being viewed where I am interested in counting the number of occurances within a date range and having that value populate a specific field. By selecting the advisor, the fields are to automatically populate with weekly numbers.

I think I have stumbled onto what I need, but I am still not able to make it work, however I think it is closer to what I am looking for.

Without getting complicated I am trying to go about it by using ValueCount and FilterValues, but I am coming up with Zero or no value at all vs the actual count.

[Lee Smith]

All Three Topics have been [color:red]Merged