2 Replies Latest reply on Oct 10, 2012 9:28 AM by SamOsman

    Creating a conversion rate



      Creating a conversion rate




           I'm currently putting together a way to track leads from leaflet deliveries, I've got it all up and running in Excel, and have now moved it into FileMaker and it's all working there as well. The only area I've got stuck on is working out the conversion rate in FileMaker, something that was very easy in Excel.

           So the question I have is how do I add number in certain columns but not all the columns, and then divide it by another total amount of a column.

           So I have 'leaflets delivered' column, which i need to add up all the rows in that one column and then divide it by the total rows in two other columns to work out the conversion rate.

           Does anyone know how to do this? 



        • 1. Re: Creating a conversion rate

               To get the total of the same field over multiple records, you have two options, the Sum Function or a "total of" summary field. In similar manner, you have a count function and a "count of" summary field to count the records if that should be needed.

               I'll call your "columns" to be totaled Field1, Field2, Field3 for this example.

               define 3 more fields, sTotalFld1, sTotalfld2, sTotalFld3 as summary fields. Specify sTotalFld1 as the total of Field1, sTotalfld2 as the total of Field2 and so forth.

               Define a calculation field, conversionRate, as:

               stotalFld1 / ( sTotalfld2 + sTotalfld3 )

               This is just the most basic solution to what you describe. The Summary fields will compute the totals of all records currently present in your found set for this table. If you perform a find to pull up a different set of records, these fields will display a different set of totals and a different result in the converstionRate field.

               If you want to compute these values for groups of records to get different sub totals and different conversion rates, this can also be done if you sort your records, add sub summary layout parts and use the getSummary function.

               Note: it would be a good idea to look up aggregate functions (sum, count, etc), summary fields, and getSummary in FileMaker help to learn more about them.

          • 2. Re: Creating a conversion rate

                 Sorted it, thanks for your help! :D