You could try
ExecuteSQL ( "
SELECT COUNT ( DISTINCT ( Month ( theDate ) ) )
WHERE theDate BETWEEN ? AND ?
" ; "" ; "" ; YourTable::startDate ; YourTable::endDate
You could do the same natively, but not without at least one helper calc field, like
cMonthName = MonthName ( theDate )
ValueCount ( FilterValues ( "January¶February¶March/* etc. */" ; List ( SelfJoin::cMonthName ) ) )
You could set up a related table with one record for each month number. Each record would also have a year field. In the attached, I filled this table once with data in each of 12 records, then created 2 button activated scripts (Add Year and Subtract Year) to change the year values. In your transaction table create Month number and Year calculation fields for your transaction dates and set the relationship. This way you always have a "rolling" view of any 12-month range.
MonthTest.fmp12.zip 69.6 K
I apologize for late reply but I appreciate answers from erolst and Allen.
I'm still having challenges that will take a bit of explanation but I wanted to say thanks while I had a minute.
See the attached file based initially on Allen and including erolst calc to count unique months. For some reason, the calc is not calculating the number of unique months in the relationship correctly and I can't figure out why.
What am I doing wrong here? (For example, see record 70 on layout "Testing Temp")
My ultimate goal is to summarize (chart) values for 12 calendar periods with each period consisting of 12 months data and in ascending date order.
e.g. the 12th period would be the current month. It would include the current month and the previous 11 months for a total of 12 months of data. The 11th period would be the previous month and 11 months of previous data, etc.
Mtest Clone.fmp12.zip 210.2 K
What am I doing wrong here?
The field is calculating OK; but the field instance you're displaying on the layout comes from the self_join TO, not from the layout TO. (Two leading colons in the field name indicate a related field, btw.)
Change this to use the same field, but from the "Current table (“TRANSDATA”)" TO.
Great. That worked.
This is a recurring question for me. I must be looking at the relationships or context incorrectly. In this case, I was thinking that I wanted to count the number of unique months in the join table so the field needed to come from the join table.
I’m thinking of the join as a type of filter that creates a subset of the base table so the field that is counting the values needed to count values from the join. Obviously, this is wrong but I’ll be darned if I understand why. One of these days, it will all make sense.
Thanks for your help.
I don't know if the attached will help you because I'm not sure I understand what you are trying to accomplish. But the attached has the 1031 transaction date records from the file you attached earlier (in the Transaction table). The related MonthCount table shows you the transaction date count for each month between the start and end dates based on any given transaction date entered in the Trans Date field.
MonthTest_2.fmp12.zip 84.9 K
Here’s a more complete explanation of what I’m trying to do.
We collect customer satisfaction surveys. Customers give us a score of 1 - 10. We follow the Net Promoter Score system. Scores of 9 or 10 are grouped as “Promoters.” Scores of 6 or below are considered “Detractors.” The Net Promoter Score is the % of Promoters minus the % of Detractors. This calculation isn’t a problem. I ‘m just explaining the system.
Some months we get 50 surveys, some months we get 200 so monthly comparisons can be misleading. To offset this difference, I want to show the trend over time using a column chart with 12 columns. Each column is a 12 month period. So, assuming the right most column is for the 12 month period ending May 2016, it would show the Net Promoter score for all surveys received since June, 2015.
The column immediately to the left would be for the 12 month period ending April 2016. It would show the Net Promoter Score of all surveys received since May 2015 … and so on. The leftmost column (1 out of 12) would be for the 12 month period ending June 2015 and would show the Net Promoter Score of all surveys received since July, 2014.
This type of chart eliminates seasonal fluctuations and situations like ours where the number of surveys received each month varies. Here an extreme example. Assume we get one survey with a score of 10 in a specific month. It would be very misleading to post this value on a chart all by itself.
I hope this helps clarify my end goal. Based on your example file, I think I should eliminate the self-join table and create my reports from a new table (Periods or Month/YR).
Thanks again for taking the time to help.
Thanks for your help. The attached is a modified version of the last file you sent. Thank you.
This one includes the type of chart I described in my last email. It is based on random data. In the real world, the chart would show actual trends over time.
When viewing the chart, remember that each column represents a 12 month period. The x axis shows the ending date of the period so it includes data from the previous 12 months.
Thanks again for your help.
MonthTest_2 Copy.fmp12.zip 140.7 K
Your chart has 13 months; it starts one month too early. I think this is more complicated (and has too many fields) than it needs to be. Search this site for Virtual Lists. This seems a perfect candidate for a utility table and a script using ExecuteSQL.
When viewing the chart, remember that each column represents a 12 month period.
That's what I understood from one of your earlier posts.
But according to your legend and this:
The x axis shows the ending date of the period so it includes data from the previous 12 months.
… each column represents a single month, not 12.
What is it?
You're right. It does show 13 periods. I'll make that change later. Just wanted to show the concept.
Sent from my mobile phone, so please excuse the brevity.
I did a quick test before uploading and I'm pretty sure each column includes 12 months of data. Can't test now. The x axis label should be something like "12 Month Period Ending"
Thanks for follow up.
Sent from my mobile phone, so please excuse the brevity.
ExecuteSQL is beyond my ability for this challenge, so I chose the brute force method in the attached. The data table has all 1031 records from the files you sent. The Period Start and End fields are figured from the Current Date, so there are 12 periods (months) going back in time. Each data record is placed in a date range that relates to a Period Start and End.