6 Replies Latest reply on Dec 19, 2014 1:06 PM by Sage

    Sub Summary in Comparison to the same week a year ago

    Sage

      Title

      Sub Summary in Comparison to the same week a year ago

      Post

      I currently have a script that is set up to do the following: 

      Go to Layout ["Summary Status"]

      Enter Find Mode [ ]
      Set Field [Table1:Status ; "Active"]
      Set Field [Table1:State ; "StateName"]
      Set Field [Table2:Action ; "Registered"]
      Set Field [Table2:Year ; Year ( Get ( CurrentDate ) ) ]
      Perform Find [ ]

      Inside the layout I have 16 different Summary calculations.  I run a company with clients in 4 states and need to track those states the same way so usually the only variable that would change would be the state name.

      My script currently works just fine, but I want to expand it to included last year's data (which may mean re-writing the script completely).

      Once I get it set up correctly, I would like to compare each of those summaries to the same date, but a year earlier.  I have not been tracking those summaries on a daily/weekly basis because I don't know how to set it up.  Moving forward I would like to log the summaries based on a script that runs when I close the database.

      Any help would be appreciated.

      Thanks.

        • 1. Re: Sub Summary in Comparison to the same week a year ago
          philmodjunk

          Inside the layout I have 16 different Summary calculations.

          Please describe those calculations. There are several very different ways to get a "summary calculation" and in addition, those might be grand totals, totals of a subset of your found records or totals of a set of related records. That's necessary before anyone can comprehend what type of "Comparison" to the same date a year ago might be possible here.

          Moving forward I would like to log the summaries based on a script that runs when I close the database.

          And what do you mean by "log a Summary"? Are you trying to compute sub totals and then save them in another table when the file is closed?

          • 2. Re: Sub Summary in Comparison to the same week a year ago
            Sage

            Sorry for the lack of clarification.

            Here is a screenshot of what I'm talking about, that will hopefully help clear up some confusion.  The summaries are calculated by " = Total of FieldName."

            In regards to logging a summary, I would like to just keep track of where my numbers are at the end of every day.  I just haven't ever programmed anything like so I may not be asking the right question.  Right now, I don't track that on a a daily basis.  Once I check my numbers, they aren't stored anywhere so there is no way for me to look back at a previous day/week/month/year to compare.  I think what you suggested may be what I'm trying to do.

            • 3. Re: Sub Summary in Comparison to the same week a year ago
              philmodjunk

              Does each row of data in your table represent a single record or a group of records? If a group of records are these a group of records in the layout's current found set or from records in a related table?

              You don't have to log your summary data just to be able to "look back" at how you did previously as you can use relationships and/or finds to pull up and summarize past records to show what you had on a past day, week, month, quarter or year.

              • 4. Re: Sub Summary in Comparison to the same week a year ago
                Sage

                Each row represents a group of records. Column 1 is from a portal that contains a related table.  Column 4 is from a different portal that contains info from a different related table. For Columns 2 & 3, I have a checkbox on each person's record that accounts for their photo being submitted and one that accounts for all of their forms have been sent in.  The first column that counts the total registered reads the data from a portal on their record that shows their history with our organization.  

                That's great that I don't have to log all of that information.  I'm all ears on how to implement that.

                Thanks for your help.

                • 5. Re: Sub Summary in Comparison to the same week a year ago
                  philmodjunk

                  Each answer spawns more questions. I really need to know the data model that you have in place before I can offer an answer. Can you upload a screen shot of Manage | Database | Relationships and then include an explanation of which "box" in that graph represents your layout and which represent the portals that you have mentioned?

                  • 6. Re: Sub Summary in Comparison to the same week a year ago
                    Sage

                    I circled the primary data box and labeled it Main.  The next 2 circles represent the tables that are used in the portals.