9 Replies Latest reply on Sep 19, 2016 8:14 AM by bigtom

    Summation for a fixed period

    scheip22

      Dear all,

       

      Does someone have a solution for next problem? 

      Layout 1.

      Here you could find a summary list of the daily and total workhours of a machine.

      . Date 1 _            Daily hours_                     

                                     Subtotal_

      . Date 2 _            Daily hours _

                                     Subtotal_

      . Date 3 _            Daily hours_                     

                                     Subtotal_

      . Date 4 _            Daily hours _

                                     Subtotal_

      . Date 5 _            Daily hours_                     

                                     Subtotal_

      . Date 6 _            Daily hours _

                                     Subtotal_

      .                              Total_

       

      In Layout 2. A calculation should be made of the workhours from a specific component (of the machine), from the date of installation till now. How could I determine the workhours (Layout 1) of a fixed period e.g. from date 2 till date 4?

      In some cases, the defined date (layout 2 :: installation date) is in the range of the summary period, but not literally mentioned in the summary list.

      Thanks a lot!

        • 1. Re: Summation for a fixed period
          philmodjunk

          And how is the data stored in the table? One record per machine per day? With the hours entered as a number?

          Or are there multiple records each day that log machine time for a given machine?

           

          And you are doing this for more than one machine aren't you?

          • 2. Re: Summation for a fixed period
            scheip22

            Hi,

             

            Each day one record is made for the machine, with the hours entered as a number.

            It is for more machines, but that is no issue. In my main page, I have to select the machine, before I can get acces to the logbook page. For one machine there are different components (lay-out 2).

            • 3. Re: Summation for a fixed period
              philmodjunk

              A summary field that computes the "total of" your Hours field will give you your total, but in your example, if you have just one record per day, the Hours and the subtotal would appear to show exactly the same value.

               

              You can perform a find or use other methods such as Go To Related Records (if you have the correct relationship set up and start from a table of machines) to get a found set of just the records you want for computing totals, averages and other aggregate values that you might want to produce using summary fields.

              • 4. Re: Summation for a fixed period
                bigtom

                You can use a found set as philmodjunk suggested or have a way to select what you want and use ExecuteSQL("SELECT SUM())" to get the totals and put that in a global variable or field.

                • 5. Re: Summation for a fixed period
                  scheip22

                  Hi guys,

                   

                  Each record is imported by this lay-out. In this case the operational time of an aircraft. FH = Flight hours (operational hours of the aircraft).

                   

                  add record logbook copy.jpg

                   

                  After that, the data will appear in the list, where subtotals are created and the total operational time are calculated by a sub summary report.

                  Logbook list copy.jpg

                  12/09/2016 shuould be 12/09/2010. Subtotals appear when i click on sort by.

                   

                  In the next lay-out, the hours in operation should be calculated.

                  In this example the machine is in operation for 40028 hours, since 12/09/2010.

                  The compressor blade is installed at 16/09/2016 (installation date, lay-out 2).

                  At that moment the total FH are 40010 hours . So the hours in operation are 18 for the compressor blade. (total - subtotal)

                  Component life tim copy.jpg

                  To make this calculation happen, there have to be an interaction between the installation date, the total FH, and the subtotal FH at the moment of installation. But how could i create this?

                  How could you request automatically the subtotal for a given date?

                  • 6. Re: Summation for a fixed period
                    bigtom

                    You may want a button or a script trigger that will run on layout enter to calculate the value. You could use an unstored calculation as well. Done properly it should still be fast enough. I am not sure how the tables are setup, but here is a rough example.

                    Let(

                    [

                    msn = yourtable::MSN;

                    install = yourtable::InstallDate;

                    pn = yourtable::PN;

                    fhinstall = (whatever field is holding the FH data for the install date if it is the same or related record or use the next line);

                    fhinstall = ExecuteSQL ( "SELECT FH  FROM yourtable WHERE InstallDate = ? AND PN =? AND MSN =?"; ""; ""; install, pn, msn); (if you don use this you can also remove the pn variable)

                    th = ExecuteSQL ( "SELECT SUM( FH ) FROM yourtable WHERE MSN = ?"; ""; ""; msn);

                    result = (th - fhinstall)

                    ];

                    result

                    )

                    • 7. Re: Summation for a fixed period
                      bigtom

                      I think an unstored calc would be fine in this case. Sometimes they work out better in a case like this where you need live fresh data.

                       

                      You can use this to control conditional formatting to highlight low hours left and manage service.

                      • 8. Re: Summation for a fixed period
                        scheip22

                        Hi Tom,

                         

                        I think you gave me the correct answer, but it's not working ...

                        I don't know what I am doing wrong.

                         

                        These are my tables and fields:

                        - T_AC.LOG

                             - MSN

                             - Entry.FH

                             - Entry.Date

                             - SubTotal FH

                         

                        - T_AC

                             - Current FH (= SUM (ENTRY FH)

                         

                        - T_AC.CMP

                             - MSN

                             - SN

                             - Date.Install

                         

                        Let([msn = MSN;

                        install = Date.Install;

                        sn = SN;

                        FHinstall = ExecuteSQL ( "SELECT T_AC.LOG FROM SubTotal FH WHERE Date.Install=? AND SN=? AND MSN=?"; ""; ""; install; PN; msn );

                        TH = ExecuteSQL ( "SELECT T_AC FROM Current.FH WHERE MSN=?"; ""; ""; msn);

                        result = (TH - FHinstall )];result )

                        • 9. Re: Summation for a fixed period
                          bigtom

                          A screenshot of the graph would help.

                           

                          You syntax is not good. You swapped up the table and field in the query.  select field from table.

                           

                          I would also escape all you names in the format of /"Date.Install/" for all the table and field names.  You have period in the names and that might be an issue.

                           

                          You removed the PN variable declaration and left it in the query as a parameter for SN. 

                           

                          If you set all the variables to globals for testing you can test it in the data viewer. To see what's going on.

                           

                          What table is the layout based on? Is Entry.FH the hours at install? If so you likely don't need the query and just the field name.