3 Replies Latest reply on Sep 13, 2010 6:49 PM by agedpom_1

    Running Cumulatives



      Running Cumulatives


      I have a database of purchases and wish to keep a record of purchases over the previous 90 days for each purchase day.

      My fields currently  include the date of purchase and the value of the purchase.

      What additional fields do I need and what calculations/summaries do I need to set up.

        • 1. Re: Running Cumulatives

          Create a Summary field and set it to "Total" of the Purchase Value field. Then put this Summary field in the footer (or header, if you prefer) of a List View layout. Then to see records from the previous 90 days only, create a script with the following basic steps and attach the script to a button.


          Set Variable [$_90_days ; Get ( CurrentDate ) - 90 & "..." & Get ( CurrentDate ) ]

          Perform Find [Restore] // The Find parameters should be Field: Date and Criteria: $_90_days


           When you click the button, you'll see all the records for the previous 90 days and the Summary field will show you the cumulative total of the Purchase Value field for those 90 days.




          • 2. Re: Running Cumulatives

            I prefer this scripted approach:

            enter Find Mode[]
            Set Field [YourTable::YourDateField ; YourTable::PurchaseDate - 90 & "..." & YourTable::PurchasDate ]
            Perform Find []

            I prefer it because I don't have to double-click the find step to check and see how the variable is being used in the find request.

            Another options is to define a relationship that matches the current purchase record to all records by the same customer that are the same purchase date upto 90 days in the past. This allows a calculation field that uses the Sum function and no scripting is required.

            Define a calculation Field that returns a date, cPurchaseLess90 as:   PurchaseDate - 90

            Define a relationship like this:
            YourTable::ClientID = YourTable2::ClientID AND
            YourTable::cPurchaseLess90 < YourTable2::PurchaseDate AND
            YourTable::PurchaseDate > YourTable2::PurchaseDate

            WHere YourTable2 is a second table occurrence of YourTable.

            Now Sum ( YourTable2::PurchasAmt) will return the total purchases for this customer from this purchase records date up to 90 days in the past.

            • 3. Re: Running Cumulatives

              Thanks guys. All three were good, but I chose the relational version as i feel that the results are easier to manipulate