One possible way is with a portal filter.
Count ( SURVEYS::pk ) >= 12 )
Correction: There's really no reason for the Case ( ) function. You can just use:
Count ( SURVEYS::pK ) >= 12
Of course, this may perform poorly once you get a lot of records in the table, so you may want to look at other methods. (Ex: ExecuteSQL, etc.)
Thanks, Mike but there could be hundreds of Surveys dated between the RTM Start and RTM End
Well, in that case (I thought we were dealing with months), you could do this:
Create a new relationship using a global date field, with a <= predicate to the Date in the SURVEYS table. Set that date to RTM Start. If there's at least one record in that relationship, then you have data going back to the RTM Start date.
You need to relate the two tables by Survey Date ≥ RTM_Start and Survey Date ≤ RTM_End.
Then you just need to have summary records in the Survey table that can be called by the Months table. From there you can manipulate at will in either the Surveys or Months table.
I've attached an example database where the Months table has a calculation to divide SurveyTotal by SurveyCount.
Surveys-months.fmp12.zip 68.6 K
But from which context / table?
I currently have this relationship.
MONTHS::RTM_End_Date ≥ SURVEYS::SurveyDate
MONTHS::RTM_Start_Date ≤ SURVEYS::SurveyDate
MONTHS::RTM_End_Date is a date field in the form of mm/01/yyyy.
MONTHS_Start_Date is a calc subtracting 11 months from RTM_End_Date
This isn’t working. It’s matching records in SURVEYS with a SurveyDate greater than the RTM
Is MONTHS_Start_Date a date (i.e., is the result of the calculation a date)?
Yes. MONTHS::Start_Date is a calculation that subtracts 11 months from MONTHS_End_Date. Here’s the calc — Date (Month(RTM_End)-11 ; 1 ; Year(RTM_End) ) The result is set to DATE.
MONTHS is simply a table of dates one month apart starting with 5/1/2013. This date is stored in MONTHS::End_Date
MONTHS is related to SURVEYS with the following
MONTHS::Start_Date ≤ SURVEYS:Survey_Date
I want to create a relationship that finds all of the Surveys between a date range. I’ve assumed that I need to do this from a dedicated table (MONTHS) in order to create a column chart showing Months on the x axis and average scores on the Y.
I'm still missing why the portal filter I gave you (the second one) wouldn't work ...
I may be missing it too.
If I get the right relationship set up from MONTHS to SURVEYS, in some months, there could be hundreds of Surveys with a Survey Date between the date ranges in MONTHS.
Foe example, assume the following
MONTHS::EndDate = 7/1/2015
MONTHS::c_RTMStartDate = 8/1/2014
In SURVEYS, there could be hundreds of individual surveys with dates that fall into this range so I’m not getting how the filter — Count ( SURVEYS::pK ) >= 12 — would work.
What is the >= 12 comparing?
Just to be clear, the end result is a column chart with Months along the x axis and average score along the y axis. Assume one of the columns is labeled July 2015. That column will show the average scores from all of the surveys from Aug 2014 - July 2015. The next column would be Aug 2015. It would plot the average of all Surveys between Sept 2014 - Aug 2015. So, each column represent 12 months data.
Does that help?
Thanks for giving this so much time / thought.
I was a little curious and did a test. Thought it was a full 12 months, and then I counted (Year-1).
It is possible, I do not understand the problem here. :-)
BTW. U have to set the date format before test. I have EU format: YYYYMMDD. I have not test the US yet.
This i calculate two related value from a input date:
1: From date= Input Date- 1 year
2: To date = Input Date
Then those are related to Date in DATA.
Meny::From date =< DATA::Date
Meny::To date => DATA::Date
01/01/2016 = 01/01/2015-01/01/2016
I did a "Let" function on "From Date" like this. Because I did not know what date format U have I put in a list function:
Sep = "-" ;
XYear = Year ( DateIn ) - 1 ;
XMonth = Month ( DateIn ) ;
XDay = Day ( DateIn ) ;
USFormat = XMonth & Sep & XDay & Sep & XYear ;
EUFormat = XYear & Sep & XMonth & Sep & XDay ;
DateOut = Case ( DateFormat = "US Format" ; USFormat ; EUFormat )
Mtest.fmp12.zip 69.3 K
Well - maybe. Could also be that I’m complicating things. Wouldn’t be the first time.
Assume two tables - Months and Surveys.
MONTHS has 48 records
Rec # RTM_End RTM_Start
1 6/1/14 7/1/13
2 7/2/14 8/2/13
3 8/2/14 9/2/13
48 5/28/18 6/28/17
SURVEYS has about 4,000 records and more are added every month.
Each record is a Survey with a date and a score.
I want a column chart showing a trend in average scores from Surveys. I’m assuming it would have to come from MONTHS by a relationship with SURVEYS.
For each record in MONTHS, I want the average of scores from SURVEYS for if the Survey Date is between MONTHS::RTM_Start and MONTHS::RTM_End — IF there are at least 12 months of data in Surveys prior to MONTHS::RTM_End.
If I run this manually, searching SURVEYS::SurveyDate for a range between 8/1/2013 … 7/1/2014, I find 938 Surveys with an average of 7.57. When I advance one month (9/1/2013 -8/1/2014), I get 980 Surveys and and avg goes to 7.97. And so on …
I could do this manually (obviously) but I would also like to do the same calcs for different versions of our product. We have 9 so that would require quite a few manual calcs and I just KNOW FMP can do this!
I read your post in my email client and didn't see the attachment or much of your content so my response below may not seem responsive. I'll look at your test file now. Thanks
You need a second relationship. (I should have said "relationship", not "filter" before.) You have to be able to determine whether there are 12 months' previous data, and you can't do that with the relationship you have.
I'm also not 100% certain what you're looking for when you say, "12 months of data". Do you mean:
1) there are data in every one of the preceding 12 months?
2) there are data in the start date - 12 months?
Those are two different questions. If it's the latter, then all you have to do is set a date field = Start - 1 and build a relationship on that. OTOH, if it's the former, then you need to isolate whether there is data in each of the preceding 12 months. That would be more complicated, but still manageable.