3 Replies Latest reply on Nov 24, 2009 3:46 AM by kirvis

    Using an "if function" within an aggregate function.



      Using an "if function" within an aggregate function.


      Hi all,


      I would like to know if it is possible to conditionalize the Sum function. I have a table with a lot of processes, which have a category tag. I would like to be able to summarize values only from rows which have a certain category tag. Because the Sum calculation will be in another table, it is not possible to use subsummary values.


      It would have to be something like:



      Sum ( 

      If (

      connect_table_processes::process category = "production and packaging" ; 

      connect_table_processes::GHG ; 




      In words: Summarize GHG values from table connect_table_processes from rows which have value "production and packaging" in process category.


      I have tried the calculation below first, but that does not work unfortunately. 


      If (

      connect_table_processes::process category = "wholesale" ; 

      Sum ( connect_table_processes::GHG ) ; 


      Is this possible? 


        • 1. Re: Using an "if function" within an aggregate function.
             Filemaker has no SumIf() function. You can either use the Sum() function over a filtered relationship, or define a recursive custom function to aggregate the values meeting the criteria.
          • 2. Re: Using an "if function" within an aggregate function.

            Howdy kirvis,


            You can do this by splitting off the "if"s into separate fields.


            Define a new field in your Connect_table_processes called "Newfield" and set the field type to Calculation.

            Define the calculation as the following:

            If (connect_table_processes:: Process category = "production and packaging" ; 

            connect_table_processes::GHG ; 



            {Note: Consider the "Case" function instead of the "If" function if you're looking at "production and packaging" and "wholesale" and others...there is a good explanation of "Case" in the FMP help menus} 


            Perhaps with a case function this might look like:

            Case (connect_table_processes:: Process category = "production and packaging" ; connect_table_processes::GHG ;

            connect_table_processes:: Process category = "wholesale" ; connect_table_processes::WSL ) ;



            Now change your Sum function to be: Sum(Connect_table_processes::Newfield)


            This approach lets a calculated field {Newfield} serve as your "if" parts, letting you simply Sum the results.  The "Newfield" will be set, record by record, to the value that particular record contributes to the Sum that you want.  Now your "Sum" will simply be of that field across all records and not need the conditional "If" nested into the Sum Function.


            Edit: clarified wording based on PM.  I could not access your file at public.me.


            Is this enough to get you running?

            • 3. Re: Using an "if function" within an aggregate function.

              Ninja, you are a true genius!


              I made a field for every step in the supply chain with a conditional If calculation. Then I used the Sum function on this field in the other table and it works like a charm! Thanks for that!


              I also found another solution here with the custom TypeSumField function, so it seems I have a luxury problem since now I have to choose.


              Thanks again!