1 2 Previous Next 20 Replies Latest reply on May 2, 2014 6:38 PM by erolst

    Aggregate functions not working?

    hjgunn@mmedia.is

      I'm using aggregate functions to calculate the Sum, Average, Standard deviation etc. of some items in a related table.

       

      I have two tables, Items and Summaries

       

      The Items table has 5 fields: Key_ProcessYear, Input_Item1, Input_Item2, Input_Item3 and Input_Item4

      The summaries table has 11 fields: Key_ProcessYear, Sum_Item1, StDev_Item1 ... Sum_ItemTotal, StDev_ItemTotal.

      I then have a relationship based on Key_ProcessYear

       

      ===

       

      Calculating the Summaries for each item through the relations works fine.

      Calculating the StDev for each item through the relation also works fine.

       

      As I suspect you all know, doing something like Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 ) does not give me the sum of Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4.

      It actually returns Items::Input_Item1 + Items::Input_Item2 + Items::Input_Item3 + Items::Input_Item4.

       

      ===

       

      I know I can write a script to perform the calculation and run it everytime needed.

      That is everytime I display the layout containing the aggregate results and everytime I change a value in the items table if a layout showing calculations based on the edited value is being displayed...

       

      But is there really no simpler solution?

        • 1. Re: Aggregate functions not working?
          mikebeargie

          Your calculation is formatted incorrectly, it should be something like:

           

          Sum( Items::Input_Item1 ) + Sum( Items::Input_Item2 ) + etc...

           

          It might be easier to write an ExecuteSQL Query instead, but performance can suffer if you string too many aggregate functions together in ExecuteSQL.

           

          The other option would be the GetSummary() function.

          • 2. Re: Aggregate functions not working?
            erolst

            hjgunn@mmedia.is wrote:

            As I suspect you all know, doing something like Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 ) does not give me the sum of Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4.

            It actually returns Items::Input_Item1 + Items::Input_Item2 + Items::Input_Item3 + Items::Input_Item4.

            You lost me there somehow, but it is possible that you mean …

             

            Sum (

              Sum ( Items::Input_Item1 ) ;

              Sum ( Items::Input_Item2 ) ;

              Sum ( Items::Input_Item3 ) ;

              Sum ( Items::Input_Item4 )

            )

             

            EDIT: Think about normalizing the usual suspects of input_item1, input_item2, input_item3 … input_itemN into their own related table of InputItems that you connect between Items(?) (or whatever your main entity is) and the Summaries table.

             

            This would make things like the above easier.

             

            EDIT 2: OK, my original answer makes no sense.

             

            You need to either use a script find the appropriate Items records by year and summarize their values, or create a Years table, relate that to Summaries via year and Sum() up the values via that relationship. (No additional importing necessary, IINM.)

            • 3. Re: Aggregate functions not working?
              hjgunn@mmedia.is

              Thank you for replying Mike

               

              But you didn't understand my post - I think.

               

              I'm actually mentioning as an example for the limitation of the aggregate function that

               

              Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 )

              does not return

              Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4

               

              It only returns the summary of the the four fields in the first record encountered through the relation.

               

              ===

               

              Getting around this limitation is easy in regards to the Sum and probably the Count, Min, Max and Average functions but my dilemma is that I need to get the combined StDev value of all the fields in all the records for the period of one year.

               

              In Excel this is easy as you can create one column which contains the items from the four other columns.

              And then you calculate the StDev for the combined column.

               

              ===

               

              This would equal that I could use an additional table in FM, link to that from the summaries table and calculate the StDev for the total by importing into one field in it the values from the original items table creating a new record for each instance from the items table

               

              Or run a script that loops through all the records and fields of the original items table calculating the StDev for the Totals StDev in the summaries table.

              • 4. Re: Aggregate functions not working?
                hjgunn@mmedia.is

                Gentlemen   :-)

                 

                Your comments at least pointed me to one solution to calculate the overall StDev for multiple fields:

                (Besides looping through them)

                 

                StDev(

                List ( Items::Input_Item1 ) & "¶" &

                List ( Items::Input_Item2 ) & "¶" &

                List ( Items::Input_Item3 ) & "¶" &

                List ( Items::Input_Item4 )

                )

                 

                I don't know what happens to speed when calculating this on a few thousand fields but if anyone has thougths in that regard then I would welcome those views.

                 

                Also views on whether this method is to be preferred over looping would be welcome.

                 

                ===

                 

                Thinking further on this I would suspect that the List function has a built in low level looping function that might be a tad faster than the Loop script step.

                Although the List function should then be a bit more demanding on memory if used on large quantities of items

                 

                Any comments on this would be welcome as well...

                • 5. Re: Aggregate functions not working?
                  mikebeargie

                  You should test both methods to see what works best for performance. I anticipate that the function you list will be faster than a looping script, but I've been wrong before. If you do not expect your data to change that often, it might be faster to cache your results (rather than calculating in realtime), or by performing an update (set field or otherwise) on-demand.

                   

                  Also, if you're using Filemaker Server 13, the new "Perform script on server" might be a very handy script step for you.

                   

                  Alternatively to chaining the results of List() functions together, you might be able to consolidate this all into a single ExecuteSQL() statement, such as:

                   

                  StDev( ExecuteSQL("SELECT Input_Item1 + '' + Input_Item2 FROM Items WHERE Key_ProcessYear = ?"" ; "" ; Summaries::Key_ProcessYear) )

                   

                  Not sure if that would be any faster than your above, but might be worth it to test and try it out!

                  1 of 1 people found this helpful
                  • 6. Re: Aggregate functions not working?
                    hjgunn@mmedia.is

                    Thank you Mike

                     

                    I will look into this when I get some data imported into the solution and see what.

                     

                    ===

                     

                    Erolst - The dilemma here was that I needed to find a solution that would work with any of the aggregates functions.

                    Not just the sum function.

                     

                    I just used the sum function as an example.

                    • 7. Re: Aggregate functions not working?
                      hjgunn@mmedia.is

                      Mike...

                       

                      The thought about run script on server is to prevent all the data to be downloaded to the client machine and therefore run faster?

                       

                      But unless I'm misunderstanding something you cannot trigger a script to run on the server from a client.

                      As I understand this process you can only trigger a script on server from the servers scheduler.

                       

                      Please correct me if I'm wrong.

                       

                      However, if I use a script I need to run it everytime the specific layout is opened and also if the layout is being displayed and someone changes one of the values the StDev is based on.

                      • 8. Re: Aggregate functions not working?
                        mikebeargie

                        Perform script on server is executed from the client’s context. The actions of the script called are “performed on the server”, if the result is updating your stored values, then the values are updated on the server and you only need to refresh your local copy to see the updated values.

                         

                        But all the calculation horsepower is taken care of on the server, NOT the user, so you get all the performance gains of not having to pass data to the client to perform calculations.

                         

                        Tim Dietrich wrote a good blog post about it:

                        http://timdietrich.me/blog/filemaker-13-perform-script-on-server-insanity/

                         

                        And there’s a good video tutorial by Matt Petrowsky on it as well:

                        https://www.youtube.com/watch?v=jEpDFFuBl_Q

                        • 9. Re: Aggregate functions not working?
                          erolst

                          hjgunn@mmedia.is wrote:

                          StDev(

                          List ( Items::Input_Item1 ) & "¶" &

                          List ( Items::Input_Item2 ) & "¶" &

                          List ( Items::Input_Item3 ) & "¶" &

                          List ( Items::Input_Item4 )

                          )

                          Note that …

                           

                          1. You can use List ( List ( field ) ; List ( field ) { ; List ( …) } )

                           

                          2. You would need to convert the result into a format that StDev() can use, with

                           

                          Let (

                          result = see above ;

                          Evaluate ( "StDev ( " & Substitute ( res ; ¶ ; "; " ) & ")" )

                          )

                           

                          which is probably quite slow.

                           

                          EDIT:

                          1. StDev() doesnt want necessarily a field as input; it wants bare values …

                          2. … which is why your "correct" solution won't work, since you're passing a List, but StDev want's semi-colon delimited values

                          3. I's not a good idea to declare a $var in Let(), unless you a) reset it later, b) know what you're doing …

                          1 of 1 people found this helpful
                          • 10. Re: Aggregate functions not working?
                            erolst

                            Wow, whenever I try to reply, I find that the post I'm replying to has just been deleted from under my nose. So here goes:

                            hjgunn@mmedia.is wrote:

                            Which makes me wonder if I'm limited to sending only a fixed number of variables as there doesn't seem to be a loop function available for the custom functions.

                            Write a recursive CF. Not saying that's the solution to your problem, but there's your loop. You would need to pass the field names as string (List(), anyone? ) and parse that out, since you can call (and define) FM functions only with a fixed number of mandatory arguments/parameters.

                            • 11. Re: Aggregate functions not working?
                              beverly

                              This thread has gotten confusing!

                               

                              I don't know if this helps or not:

                                   <http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/>

                               

                              it's a way to simply how aggregates for related children are displayed in parent record. Perhaps "filtering by year" will give you what you want?

                               

                              Beverly

                              • 12. Re: Aggregate functions not working?
                                hjgunn@mmedia.is

                                I had already found out that if I enter more than one field into the aggregate functions it only works on the first record encountered.

                                 

                                But I need to calculate the overall StDev for 4 fields in all records belonging to each year, just as is done in an already existing Excel spreadsheet.  ;-)

                                 

                                I had  hoped there was some way to do this just using functions. But unless I'm mistaken FM doesn't allow this.

                                 

                                As I have one record for each year in the summaries table and a working relationship based on the process year to the items table I decided to go for a script that runs in the summaries table.

                                 

                                And on the press of a button it then recalculates those values that cannot be calculated using Calc fields and functions.

                                 

                                ===

                                 

                                The reason I was looking for a function based version of this was that I suspected that, that way it might calculate faster than when running a script.

                                So all my previous posts are speculations of mine that proved to be wrong it seems.

                                • 13. Re: Aggregate functions not working?
                                  beverly

                                  I'm not sure if you looked at the article. I propose not using the functions, but the summary fields in the child records. Then use filtered portals to show what you want in the parent record(s). Did you test this?

                                   

                                  added: and you can have a summary field of a calculated field.

                                   

                                  Bverly

                                  • 14. Re: Aggregate functions not working?
                                    hjgunn@mmedia.is

                                    I read the article but somehow I feel it is an extension of what I can already do with most of those functions besides the StDev.

                                    ADDED: Although I can see how using the sum functions locally and just display their results through the relation might work faster. Unfortunately that solution would only work for one field at a time unless I'm mistaken.

                                     

                                    ===

                                     

                                    As I said I need to calculate the StDev from all the values in four fields as if they were all in one field.

                                     

                                    I don't see how to achieve that using filtered portals although they seem excellent for calculating various different values.

                                     

                                    In my problem I have the records and the key needed to display what is on the other side of the calculation.

                                     

                                    Due to the structure of the Aggregate functions they only allow me to work on data from one field at a time without using workarounds.

                                     

                                    The workarounds for most of the aggregate functions can be solved by using functions alone however when calculating the standard deviation and Variance you must have access to the entire population of valuables behind the calculation. And the values must all be in the same field.

                                     

                                    So I only see two practical solutions for this...

                                     

                                    Either to copy all the values to a single field in a second table creating one record for each value present and then maintain that set of values each time the original table is added to or any of it's values changes. I mostly figured out how to do that though

                                     

                                    Or, go the perhaps "easier" way and update the results being displayed int the Summaries table manually by running a script at the click of a button or by using triggers in the fields involved to update the values if the results list is open. And also update the results list each time I open it.

                                     

                                    The script would place  the values from all the fields in a variable using the list command, then loop through the values in the variable and calculate the summary needed to create the standard deviation value for the total of the four fields.

                                     

                                    The upside of the latter approach is probably that it uses less overhead than using a special table.

                                    Especially if you need to calculate the StDev for several sets of data.

                                     

                                    If anyone has a better approach please do tell but until then this seems to be the solution to my problem.

                                    1 2 Previous Next