10 Replies Latest reply on Oct 8, 2014 11:55 AM by philmodjunk

    Calculation field based on Sub-summary Data

    sccardais

      Title

      Calculation field based on Sub-summary Data

      Post

      I want a field to calculate the number of Net New Clients added each year by subtracting the number of cancellations from the number of New Clients.

      My table includes date fields showing the date a customer becomes a customer and another field showing the data of cancellation if any. Two more calculation fields get the Year of the Order and the Year of Cancellation  - if any.

      Two summary fields count the number of New Clients and Cancellations by counting the values in the Date Ordered and Date Cancelled fields. 

      The two Summary fields are: s_TotalOrders and. s_TotalCancellations.

      For each year, NetNewClients should be the number of New Clients added minus the number of Client Cancellations.

      So, for each year starting 2005, I'd like to see the number of New Clients added, the number of Cancellations and the Net. Screenshot showing sample data is attached.

      The GetSummary function is not working in this case because (I think) the break field cannot be the same. The year ordered is not necessarily the year cancelled.

      I'm sure I'm missing something simple. How should I be calculating the Net New Clients?

       

      NetNewClients.png

        • 1. Re: Calculation field based on Sub-summary Data
          philmodjunk

          If s_TotalOrders and. s_TotalCancellations. do not use the same break field, how do they show the correct subtotals in the sub summary layout part?

          Is it possible that the break field and summary field are not defined in the same table? (This is the one case where the sub summary layout part will work and getsummary will fail, but there's a simple work around possible when this is the case.)

          • 2. Re: Calculation field based on Sub-summary Data
            sccardais

            All fields are in the same table.

            The summary fields don't show the correct amount in a sub-summary part but they show the correct amount on individual form layouts.

            Perhaps I should start from scratch to calculate Net New Clients. 

            Each record holds information about a company. The following fields are relevant to calculating Net New Clients.

                   
            • Status: Value List. Text. Client, Trial, Cancelled
            •      
            • DateOrdered: Date field showing the date the company became a paying customer.
            •      
            • DateCancelled: If Status is "Cancelled," the date the company cancelled.
            •      
            • c_Year Ordered: Calculation with number result. Year(DateOrdered)
            •      
            • c_Year Cancelled: Calculation with number result. Year (Date Ordered)
            •      
            • s_TotalOrders. Count of DateCancelled
            •      
            • s_TotalCancellations. Count of DateCancelled

            I suspect these fields are not structured properly to calculate Net New Clients. 

            I would like to be able to show a list report similar to the screenshot in the previous post and in a column / bar chart.

            Thanks for your help.

            • 3. Re: Calculation field based on Sub-summary Data
              philmodjunk

              It looks like the issue is that you have two dates in the same record that might require the same record contribute to the date ordered count in on row and the date cancelled count in the other.

              I'd et up a table of years with one record for each year. Use relationships to match two two different occurrences of your current table, one that matches by year cancelled and one that matches by year ordered. Calculation fields defined in this table of years can use the count function to count the number ordered and number cancelled in that year.

              • 4. Re: Calculation field based on Sub-summary Data
                sccardais

                I was just creating a table, FISCAL_YEARS when your reply arrived. Small steps / progress! See attached screenshot.

                Are you saying the calculation fields should be in the FISCAL_YEARS table?

                Can you give me an example of one of the calculations to calculate either total new orders or cancellations?

                Should the final report and chart be based on the FISCAL_YEARS table?

                • 5. Re: Calculation field based on Sub-summary Data
                  sccardais

                  Phil:

                  Please disregard my last post. After re-reading your reply and giving it more thought, I got it worked out. Thanks very much.

                  • 6. Re: Calculation field based on Sub-summary Data
                    sccardais

                    Follow Up ...

                    I now have a layout based on the FISCAL_YEARS table that correctly shows Net New Clients for all versions of our product. I would like to be able to filter this by version of the product. e.g. Net New Client for Product A, etc.

                    We have 9 versions of the product. The version is identified in a field "Version" in the ORGs table. 

                    How would you suggest I do this? I have a GLOBALS table with field: g_Versions. Is there a way to use this to restrict the Net New Clients report to one or more Versions?

                     

                    • 7. Re: Calculation field based on Sub-summary Data
                      sccardais

                      The relationships shown in the attached screenshot solved a big problem -- it allows me to calculate New New Customers for each fiscal year but it doesn't allow me to summarize the report for different versions of our product.

                      The version is defined in a field from the ORGs table but I don't see a way to get to that field from the FISCAL_YEARS table which is the only way to create the Net New Clients report.

                      Do I need to add the Edition field in FISCAL_YEARS table and change the relationship to match on both Fiscal Year and Version? Since we have 10 versions, that means 10 records for each Fiscal Year. Not a problem until I start adding additional criteria such as customer size, State, etc. Doesn't seem right.

                      Do I possibly need a different structure for these tables to allow reports showing Net New Clients summarized by other criteria located in the ORGs table?

                       

                       

                      • 8. Re: Calculation field based on Sub-summary Data
                        philmodjunk

                        Much depends on how you want your report to look like. Do you want to see a break down for each version or to select for one version and just see totals for it?

                        One option is one such record in FiscalYears for each product version and year so if you have 9 versions of your product, that's nine records for each fiscal year. That let's you show a different total for each version all in one report. A version field would be added as a second match field matching to a version field in your Orgs table.

                        Another option is to set up that version field as a global field. That limits you to seeing the data for one version at at time--as specified by you in the global field.

                        Filtered portals could also be used to filter for particular versions and summary fields defined in the Orgs table could be used in place of calculation fields in FiscalYears.

                        A final option is to use Calculation fields in FiscalYears that use ExecuteSQL to compute these totals.

                        • 9. Re: Calculation field based on Sub-summary Data
                          sccardais

                          It seems like the most flexible of the three approaches you outlined is ExecuteSQL.

                          The first option, adding nine records (Versions) for each fiscal year, would be fine but I will also want to show Net New Clients based on criteria other than Version. Don't think this is the best option.

                          The global field approach might work for more than one Version if I format the global as checkbox, convert to List and loop through each item on the list to generate the find using your Extend Found Set approach. 

                          Not sure I understand the Filtered Portal approach because I don't see a way to match on Version. I'm probably missing it.

                          I think I've seen a document describing FileMaker's implementatino of ExecuteSQL and should probably study that before deciding which approach to take.

                          Thanks for your detailed list of approaches with Pros and Cons.

                          • 10. Re: Calculation field based on Sub-summary Data
                            philmodjunk

                            The global field approach might work for more than one Version if I format the global as checkbox, convert to List and loop through each item on the list to generate the find using your Extend Found Set approach.

                            Or this field could be used as a match field and then your relationship will match to all listed records.

                            A portal would list all related records and then a portal filter would specify the version to limit the total to only that version. Make this a one row portal and put a summary field from the portal table in it and you get an aggregate value of just the related records passing the portal--such as all Orgs for the specified fiscal year using a particular version.