14 Replies Latest reply on Nov 12, 2013 3:11 PM by DerekPopovich

    SUM Function Script help - Filemaker Pro 12

    DerekPopovich

      Title

      SUM Function Script help - Filemaker Pro 12

      Post

           Hello,

           I have a table with 4 field: ID, code, Date and Value.

           ID -- code --    DATE       -- Value 

            1 -- 0001 -- 2013-01-01  -- 55

            2 -- 0002 -- 2013-01-01 --  45

            3 -- 0003 -- 2013-01-01 --  30

            4 -- 0001 --  2013-02-01 -- 50

            5 -- 0002 -- 2013-02-01  -- 50

            6 -- 0003 -- 2013-02-01  -- 50

            

           I need a script that will add the value based on the code's (manual set) and the date which will give me a result such as:

           I type in the script the code I want to add up (002 and 003) as Total

           The end result should be:

           date             -- total

           2013-01-01 -- 75

           2013-02-01 -- 100

           The script will be use in a chart so I can chart the "total" of few codes combine vs the date.

           Has anyone has any idea on how to do this?

           I try using the SUM function but I don't get the data I need.

           I have search for sample scripts and information but I am very lost,

           I have not been able to find any examples for 2 days and some help will be very much appreciated.

            

           Sincerely, DPP

        • 1. Re: SUM Function Script help - Filemaker Pro 12
          philmodjunk

               The sum function function won't work for this purpose unless you can define a relationship that matches to the specified group of records and then you can use Sum to total up the related records.

               But a summary field can compute those sub totals and no script is required to produce the needed sub totals for each group. If you need your chart to report all totals for each code for a specific day. You can specify the summary field as your y-series value, code as the x-series and in data source settings specify the option for "summarized data, groups of records when sorted".

               Then you perform a find for the records of the date required and sort them by code to group them for the chart.

               But if you want your chart to show these totals for more than one date in the same chart, but with subtotals for each date, then life get's more complex due to the limitations of this data source setting. (Can only group the charted data on a single sort field.)

          • 2. Re: SUM Function Script help - Filemaker Pro 12
            DerekPopovich

                  

                 Thank you PhilModJunk,

                 I started on that path, I have created a self join relationship for my date and code fields  and created

                 calculation field as follows:

                 DUPCheck  = If(SelfJoinTable::Code = Code; 1;0),

                 Firstvalue = If(DupCheck = 1; code; "")

                 ValueSum  = Sum(SelfJoinTable::value)

                 But I am not getting what I want, for Filemaker programers that may seem obvious from looking at the calculation, but NOT for me. The above calculation is adding up everything on my database, I change the above calculation to use my date field but the end result remain the same, trying to understand how FileMaker calculation and script works. I am trying to follow the example from http://help.filemaker.com/app/answers/detail/a_id/9610/kw/calculate%20total%20in%20portal%20rows. Can you help me fix my calculation of direct me to a link that have similar calculation examples. Sincerely, DPP

            • 3. Re: SUM Function Script help - Filemaker Pro 12
              philmodjunk

                   I really don't think sum is the right approach here--which is why I suggested a summary field instead. I would probably use that approach even if I needed to chart these subtotals for the different codes over a range of multiple dates with a data point for each subtotal for each date.

                   You don't describe the relationship in much detail but it sounds like you set up:

                   YourTable::DateField = SelfJoinTable::DateField AND
                   YourTable::Code = SlefJoinTable::code

                   If that's the case, then Sum ( SelfJoinTable::Field ) will compute the sum of Field from all records with the same date and code.

                   It would be helpful to take a moment and describe the precise chart that you want to produce in more detail.

              • 4. Re: SUM Function Script help - Filemaker Pro 12
                DerekPopovich

                     OK, 

                     I have 3 sales people entering values for their personal sales on the weekly basis Every Thursday.

                     for example : Joe has Total Sales = code 551, Bill has Total Sales = code 661, Sam has Total Sales = code 771 

                     So the database will look like this:

                     -- Code -- Date            -- Value --

                     -- 551    -- 17/10/2013  -- 5000

                     -- 661    -- 17/10/2013 --  5500

                     -- 771    -- 17/10/2013 --  2500

                      

                     -- 551    -- 24/10/2013  -- 2000

                     -- 661    -- 24/10/2013 --  2000

                     -- 771    -- 24/10/2013 --  2000

                      

                     -- 551    -- 31/10/2013  -- 2000

                     -- 661    -- 31/10/2013 --  2000

                     -- 771    -- 31/10/2013 --  3000

                     I need to make a calculation or a script that will calculate the

                     total sales for 17/10/2013 "Date" which should be $ 13,000 (for code 551, 661, 771 only)

                     total sales for 24/10/2013 "Date" which should be $ 6,000 (for code 551, 661, 771 only)

                     total sales for 31/10/2013 "Date" which should be $ 7,000. (for code 551, 661, 771 only)

                      

                     once the calculation is done, I need a line chat Value/dates that will show the total sales for each of those dates.

                     I have over 100 entries done every week from other people in the company , I collect all the KPIs for each individual and generate total summaries like the one explain above, I have about 10 line charts that I need to generate with different Code combination. I hope this explains what i am trying to do

                     Thank you for the tip on the summary field, I will search some more and see if I can work out a way to produce the result I want

                • 5. Re: SUM Function Script help - Filemaker Pro 12
                  philmodjunk

                       What I have been asking for is a description of the chart that you want. Different types of charts can require different ways to compute the needed subtotals for plotting in the chart.

                       So what kind of chart?

                       What should it look like?

                  • 6. Re: SUM Function Script help - Filemaker Pro 12
                    DerekPopovich

                         This is how is supposed to look.

                          

                    • 7. Re: SUM Function Script help - Filemaker Pro 12
                      philmodjunk

                           This only shows a single line which would appear to be the total for each week? Do you want a different line for each "code" for each week?

                      • 8. Re: SUM Function Script help - Filemaker Pro 12
                        DerekPopovich

                             That is right, I only need the Total for each week, 

                             The individual code lines are not needed.

                        • 9. Re: SUM Function Script help - Filemaker Pro 12
                          philmodjunk

                               Define a field, cWeek, of type calculation that returns date as the result type:

                               DATE - DayOfWeek ( DATE ) + 1

                               This will return the date for Sunday of the same week as DATE.

                               Specify the summary field as the Y-Series and cWeek as the x-Series.

                               In the data source settings select the options for Found set and summarized data when grouped.

                               Then be sure to sort your records by cWeek.

                          • 10. Re: SUM Function Script help - Filemaker Pro 12
                            DerekPopovich

                                 Thank you,

                                 I have to explain my problem,

                                 I can not find a way to create a summary or a sum that will add the values for code 551, 661, 771. 

                                 that will give me a total by DATE field or that it will GROUP the total by the existing values on the DATE Field.

                                  

                                  

                            • 11. Re: SUM Function Script help - Filemaker Pro 12
                              philmodjunk

                                   I'm not sure that I get this part:

                                   

                                        I can not find a way to create a summary or a sum that will add the values for code 551, 661, 771.

                                   This is what has had me mistakenly thinking that you wanted a sub total for each code until I say your chart example and then when back and re-read your initial post.

                                   Is this a situation where you have more codes than these three but only want the total from records where  one of these three codes are specified?

                                   Is your table setup, as I have assumed, such that you have one code and one number to total up in each record? Or do you have multiple fields for multiple codes and number values in the same record?

                                   Assuming one record for each value, if you want to chart the total figure only from records with one of these three codes, you can perform a find for just those three codes and thus, by limiting your found set to only records with those three values and then the chart will work as I have described.

                              • 12. Re: SUM Function Script help - Filemaker Pro 12
                                DerekPopovich

                                     Hi

                                     Assuming one record for each value, if you want to chart the total figure only from records with one of these three codes, you can perform a find for just those three codes and thus, by limiting your found set to only records with those three values and then the chart will work as I have described.

                                     Yes, I have one records for each value.

                                     I have a perform find with those records,

                                     The chart is not doing it - It keeps charting all the individual records, I change it to summary and it keeps charting the individual records without adding the value based on the DATE field.

                                     If I enter a summary field It add everything and put "Everything" on every single data point

                                • 13. Re: SUM Function Script help - Filemaker Pro 12
                                  philmodjunk

                                       Did you sort your records to group them by cWeek after performing your find?

                                  • 14. Re: SUM Function Script help - Filemaker Pro 12
                                    DerekPopovich

                                         Thank you, 

                                         I finally work out how to use the Perform Find, I though I could enter everything in there at once. But I had to do a Perform Find and then Extend Found Set.