Are you using a version of FileMaker that supports the ExecuteSQL() function?
In what context and in what type of report or layout design do you need to see these sub totals?
Do you want to see these totals for a single reporting period or in a report that lists these subtotals for multiple reporting periods?
Using FMP 13 advanced so, yes. It does support ExecuteSQL() function.
I would like to chart the results by Report_period (x axis) over time for all versions of the product (Field=ProductVersion)
Ultimately, I would like each column to display the sum of the previous 12 months New Customers divided by the sum of the previous 12 months Free Trial.
New Customers and Free Trials are both attributes of the field "Metrics".
For example, the ConversionRate plotted for Jan 2015 would be the sum of Value for records with a ReportPeriod after 2/1/2014 AND Metric = NewCustomer / sum of Value for ReportPeriod after 2/1/2014 AND Metric = FreeTrials.
Thank you for any help.
Not sure that I understand this detail:
Product_Version (value list of 10 possible products)
for all versions of the product (Field=ProductVersion)
Doe this field Identify 10 different versions of 1 product or 10 different products? If 10 different products, how to you identify the version of each of those products?
and then there is:
Is this a date field with a date that is always the first of the month?
And it takes a second post to discover that you want to chart this data. That's an important detail as some methods I might have suggested work for a summary report but not for a chart.
Am I correct that each data point on your chart is based on 24 records, one for a given value in Metric and a given value in ProductVersion over the preceding 12 months with one record for each month for that metric - ProductVersion value pair?
In otherwords for product x, you'd have 12 New Customers records and 12 Free_Trials records for each point on the chart.
Answers to your questions and more explanation ...
Product_Version: It identifies 10 different products.
Reporting_Period: This is always the last day of the month since the metrics are reported monthly.
Regarding the # of data points for the chart. The answer to your specific question is Yes. For the chart you describe, we would have 24 data points. Most of the charts we want have time (Report_Period) on the x axis and a single metric on the Y and the option to choose one or more Product_Versions for comparison. We would not plot New Customers and Free_Trials on the same chart. Instead, we simply chart Conversion_Rate. Likewise, rather than plot New Customers next to Cancellations, we'd plot Net_New_Customers.
The more I think about this, the more I think it might be easier if I stored my monthly metrics as separate fields rather than attributes of a single field. It would make it much easier to calculate derived metrics such as conversion rates, Net_Net_New_Customers for example.
In that case, I could create most of the charts easily. But, I'm not sure how to create Rolling 12 Month (RTM) charts which are important to us. Each column in the RTM is the sum of a single metric (e.g Revenue) for the current and previous 11 months. For example, the Jan 2015 column in an RTM Revenue chart would display the sum of all Revenues for the selected Product_Versions since Feb 2014. The Dec 2014 column would show the sum of Revenues since Jan 2014, etc.
In the table METRICS, assuming separate fields for Reporting_Period(date), Product_Version (text), Revenue (number), New_Customers (number), Cancellations (number) and Free_Trials (number), how would I create a relationship to sum the current and previous 11 months for each metric in such a way that it can be charted showing the Reporting_Period along x axis and RTM value along y axis for one or more Product_Versions?
Thanks for your help.