8 Replies Latest reply on Feb 21, 2014 11:20 AM by DonWalker_1

    sum based on date within a range



      sum based on date within a range


           Part 1

           Given a week ending date, a transaction date, and an amount, I would like to set the week ending date based on the transaction date. The week ending date will be the Sunday following the transaction date (e.g., if the transaction date is 2/14/2014 the week ending date would be 2/16/2014.

           Part 2

           Calculate the sum of the transactions that occurred during the week preceding the week ending date. This could be written as:

           IF(WE_Date - 6 ≤ TR_Date ≤ WE_Date; SUM(amount))

           I've tried a number of things that don't seem to work. Any suggestions would be greatly appreciated.


        • 1. Re: sum based on date within a range

               TransactionDate - DayOfWeek ( TransactionDate ) + 7

               2) Well you can't write it that way as that is not how the sum function works. For one thing, sum ( amount ) is the same syntax as Sum ( 5 ) which returns 5. Sum has to be supplied with a list of values either by listing specific fields from the same record or by referring to a field from a related table (where the "list" comes from the set of related records.)

               There are a number of ways to get the total that you want, but I need a more detailed description of what you need to do with this subtotal or I might suggest a method that works for me and not for you.

          • 2. Re: sum based on date within a range

                 Part 1 is now working - thank you

                 Part 2: For all the transactions during the week I want to add the amount for each transaction to give me the total amount for the week. If I had 8 transactions during the week for $5 each the total would be $40.

            • 3. Re: sum based on date within a range

                   Yes but this really supplies no more detail than what you had previously posted.

                   What I am looking for is how you specify these values:

                   WE_Date - 6

                   Do you want to have a date field where you enter/select a date for the week ending date, a calculation computes the Weekstart date and then you get a total of all transactions with a date in that range?

                   Another option would be to set up a summary report of transactions where you'd see a sub total for each week. The individual transactions can appear in such a report but they can also be kept from view to just get one row for each week with a weekly total.

              • 4. Re: sum based on date within a range

                     The transaction date and amount are manually entered. Then the week ending date is calculated (as you showed in your earlier message) where Date is the transaction date. Then the total of all transactions in that date range is calculated.

                • 5. Re: sum based on date within a range

                       Sorry but that doesn't answer my question. There are a number of ways to get the weekly totals that you want. Please review my last post where I describe two different layout design approaches and tell me which you want or if you want a third option.

                       a) you want to specify a date by entering/selecting a date and then you get the total for that week.

                       b) you want to see the weekly totals for multiple weeks, one total for each week, all in one report.

                  • 6. Re: sum based on date within a range

                         Option b is what I'm trying to do

                    • 7. Re: sum based on date within a range

                           Then all you need is a summary report with sub summary layout parts, the correct calculation field, a summary field and the correct sort order.

                           Define this calculation field to use TransactionDate to return the date for the first day of the same week so that we have a common value in this field for all records with Transaction dates in the same week:

                           cWeekStart : TransactionDate - DayOfWeek ( TransactionDate ) + 1 // this uses Sunday as the first day of the week, other days are possible

                           Define a summary field, sTotalAmt, to compute the total of your amount field.

                           Now set up a layout based on this table to be viewed in List view.

                           Add a sub summary layout part "When sorted by cWeekStart" to this layout. Put the sTotalAmt in this sub summary layout part.

                           If all you want is the weekly totals, remove the body layout part. If you want to list the transactions records above or below each weekly total, keep the body layout part and put the fields you want to see for each transaction in it.

                           To use this layout:

                           Perform a find for the range of transaction records that you want to see. This might be all records in the year 2014, all first quarter records, all records in January, 2014.... Whatever group you want to see.

                           Then sort your records in ascending order by cWeekStart. If you don't sort by this field, the sub summary layout parts will not be visible. With the records sorted on that field, you'll get the needed weekly totals.

                      • 8. Re: sum based on date within a range

                             Thank you very much - that's just what I was trying to do.