How do you get information out of your data? How do you report on the groups of data, such as the number of sales in a week, or the number of contacts in a region?
You get answers to these questions by learning to group your data. This is done with Finds and Sorting. When you have grouped your data you can use summary fields to generate totals, averages, counts, minimums, maximums and much more.
The sample file attached to this post contains a simple example of how you group figures into a time range ( group by hour, group by day ) and generate maximum and minimum values from them.
SubsummaryReports.fmp12 (788 KB)
this is version two of this file
1 Like
Summary fields are in our ‘avoid if at all possible’ list for use on Internet hosted solutions, with the possible exception of using WebDirect only.,
Summary fields and sub-summary parts are incredibly useful, as are the functions referring to them. However, anyone who has gone into a layout displaying a summary field with a large found set of records will completely understand why we try to avoid them.
I have posted previously, that we’ve a heavily scripted alternative using sub-summary parts and a linked table where the summary results are set by the scripts. Depending on whether the sub-summary part is leading or trailing, requires the script to know whether to put the information in the first or last record within the subset (lots of record ID and record number tracking).
I wanted to publish this as a proof of concept, but it is just too busy here at the moment. Hopefully, once we get over these large projects I’ll have time to tidy it up and make it available.
Regards
Andy
3 Likes
Summary fields are incredibly useful. I use them extensively for reporting and overview layouts (in related tables).
The datasets to aggregate are of moderate size and I have not yet observed any downsides, when using them with care.
Generating a subsummary report on a large data set is like walking knee deep through mud.
Being in find mode before entering the report layout is a sensible precaution when you have large data sets.
1 Like
(I've posted this reply over at the Claris discussions as well)
Hi Malcolm, thank you for that example data to play with. While I understand how that is working, it still won't give me what I need in this instance. This is why—
Each Usage figure in the table is the usage for that 15-minute period.
The Usage for an hour is the sum of all 4 usage numbers in the hour. I'll call that Hourly Usage.
How do I report on the minimum and maximum (etc) Hourly usage? Your example data, like my own efforts, gives me the minimum and maximum usage for the 15-minute periods in each of the hours rather than the min/max Hourly Usage for a number of hours after the sum for each hour has been calculated.
If this was my data…
Date | Time | Usage
01/08/2021 | 09:00 | 0.25
01/08/2021 | 09:15 | 0.35
01/08/2021 | 09:30 | 0.30
01/08/2021 | 09:45 | 0.10
01/08/2021 | 10:00 | 0.00
01/08/2021 | 10:15 | 0.35
01/08/2021 | 10:30 | 0.30
01/08/2021 | 10:45 |0.00
This is what I would be looking for…
01/08/2021 09:00 Hourly Usage 1.00
01/08/2021 10:00 Hourly Usage 0.65
01/08/2021 Minimum Hourly Usage 0.65 Maximum Hourly Usage 1.00 Average Hourly Usage 0.825
Our efforts so far provide me with…
09:00 Hourly Usage 1.00 Minimum Usage 0.10 Maximum Usage 0.35 Average Usage 0.25
10:00 Hourly Usage 0.65 Minimum Usage 0.00 Maximum Usage 0.35 Average Usage 0.1625
Any pointers on that?
Cheers and thanks again
Scott
I've loaded a new copy of the file, so download again.
The original did include the relationship that could be used to do this job. The new copy uses that relationship to aggregate all the values in an hour. it includes new summary fields that use the aggregate.
The trick that allows us to generate these values without the relationship is to place a summary field that is the Total of usage into a subsummary part based on the hour. You can then do a sort on Date and Hour. Modify the sort options on Hour to be based on the summary field Total of Usage, and to sort in descending order. This will give place the maximum aggregate value at the top of the list. If you modify the sort option to sort in ascending order it will place the minimum aggregate value at the top of the list. This isn't ideal for UIX but it could be perfect for a script that wants to obtain the max and min values and transfer them into a different place.
1 Like
Malcolm, thanks very much. I'll have a play with this relationship within the same table you've suggested.
Cheers
here is a sample file delivering as specified:
MinMaxHourly.fmp12.zip (88.7 KB)
2 Likes
Thanks for providing an example using the GetSummary function.
Question: Why use a nest of functions instead of the Hour function?
GetAsTime ( Int ( GetAsNumber ( MinMaxHourly::MyTime ) / 3600 ) * 3600 )
instead of
Hour ( MinMaxHourly::MyTime )
1 Like
Good point with hour function. The function I provided can break down into any fraction of hour in case required.
1 Like
