6 Replies Latest reply on Apr 24, 2012 6:20 AM by ferdi

    Question about Date and Calc

    ferdi

      Title

      Question about Date and Calc

      Post

      Hey;

      Could anyone tell me if it is possible to show Year To Date and Last Year To Date totals? 

      Example: I will sort my records (total of sales ) from 1/1/12 to 3/26/12 (today), and somewhere in my layout i want to show total amount of sales from 1/1/11 to 3/26/11(today in last year).

      Any suggestions or answers will be very helpful. 

      Thanks in advance. 

        • 1. Re: Question about Date and Calc
          philmodjunk

          A summary field that computes the total of your field will compute a year to date total if only records for the current year are present in the found set. If you pull up records from more than one year, you can still show yearly totals if you use this calculaiton field:

          cYear:

          Year ( YourDateField )

          and set up a sub summary layout part "when sorted by cYear" and then put the summary field in this part and sort your records by cYear.

          A relationship can also be used to compute totals for this year and also for the previous year.

          • 2. Re: Question about Date and Calc
            ferdi

            Thank you very much PhilModJunk;

            This is actually a good idea even thought it is really not how I wanted it but I have never thought of that way. 

            Thanks again. 

            • 3. Re: Question about Date and Calc
              philmodjunk

              It's not the only option. In fact, I briefly mentioned a second approach.

              • 4. Re: Question about Date and Calc
                ferdi

                How else would you apprach this matter?

                • 5. Re: Question about Date and Calc
                  philmodjunk

                  It depends on what you need.

                  This relationship might serve, but it depends on your database design whether it will work as I post it or need further modification:

                  Sales::cYear = SalesSameYear::cYear

                  SalesSameYear is a second occurrence of Sales that you create on the Relationships tab in Manage | database by selecting it and then clicking the duplicate button (two green plus signs).

                  Then, on a layout based on Sales, you can add SalesSameYear::sTotalSales

                  to show the yearl to date total. (sTotalSales is the summary field I described in my first post.)

                  A calculation field defined as Sum (SalesSameYear::SalesAmountField) will compute the same total.

                  You can create yet another occurrence of your table and relate it as:

                  Sales::cLastYear = SalesLastYear::cYear

                  to get the same totals for the previous year. cLastYear would be defined as: Year ( SalesDate ) - 1.

                  Note that these relationships match to all records in the table with the specified value in cYear. If you need to match more selectively we'd have to change the relationships to include one or more pairs of fields to make the match more selective.

                  In fileMaker 11, one row filtered portals can also be set up with the sTotalSales field to show different yearly totals and you then do not need to define as many self join relationships--but it's an approach that doesn't work well in every situation.

                  • 6. Re: Question about Date and Calc
                    ferdi

                    Hello PhilModJunk,

                    I just wanted to share my sollution with you on this issue (YTD sales Report). What i have figured was that I calculated date as a number by creating a new field and on my search i put "1...Current date" and that gave my the numbers for this year and also for last year for the same range. Then i created a layout with subsummary stuff. It came out beautifully:)