Report to showing summaries for two date ranges in same data set

[sandersb]

Hi

I need some guidance regarding setting up a report. I have a simple income and expense layout with a report that shows monthly sub summaries and grand total for a date range sorted by month and item code numbers.  The date range is set by global fields for the start date and the end date.  What I want to do is to have the report also show side by side a sub summary and grand total for each corresponding period one year prior.  I have attached a screen shot of the script to create the report as it is currently.  How can I get two sets of sub summaries and totals to show at the same time on this report - one for the selected date range and one for the prior year date range?  Thanks
 

[eos]

Try this (assuming you have a date field, and a summary field for expenses, say, sTotalOfExpense): 

• create a calc field cYearMonth = Year ( yourDateField ) * 12 + Month ( yourDateField ) [if you don't already have it]
• create a calc field cYearMonth_previousYear = ( Year ( yourDateField ) -1 ) * 12 + Month ( yourDateField )

• add a new TO of your Journal table, say, Journal_yearMonthPrevious, and relate it to your report Journal TO as self-join 
Journal::cYearMonth_previousYear = Journal_yearMonthPrevious::cYearMonth
(plus any other predicates you need, e.g. code number)

• create a calc field cSum_MonthYearPrevious as 
Case ( cYearMonth ≠ GetNthRecord ( cYearMonth ; Get ( RecordNumber ) - 1 ) ; cYearMonth_previousYear::sTotalOfExpense )**
• create a summary field sTotalOfcSum_MonthYearPrevious

• place cSum_MonthYearPrevious into the Sub-summary part, and sTotalOfcSum_MonthYearPrevious into the Grand Total part
• sort (also) by cYearMonth**

**exact calculation depends on your sort fields; it is to make sure that you only get one result per “group” so the summary value will be correct. If you use a trailing sub-summary instead of a leading one, you must change -1 to +1. If you use both types, you need an additional field … 

[Aussie John]

If you want a side by side report you might be able to get something workable if you select a two column layout opinion with a page break chosen for the date subsummarry

Edited May 24, 2015 by Aussie John

[comment]

I have attached a screen shot of the script
 

What exactly does the Constrain Found Set step do?

[sandersb]

Thanks to those who have responded.  

EOS - I will work through this to see if I can get it to work.

Comment - The Constrain Found Set constrains the range of Codes to a number below 300.  When I tried adding another Set Field in addition to the date range to restrict the Code range the result did not work whereas it did with this sequence as far as the current date range is concerned.  My problem is getting the previous date range  (with the restricted range of Codes) to show up at the same time in a report.

[comment]

A few notes, not necessarily in order:

1. This is much more difficult than you might expect.

2. Filemaker's native reports are arranged vertically; nothing will appear side-by-side. Therefore you must choose between two options:

(a) Show the comparison month value under (or above) the actual month. In this scenario, the records for both months will be part of the found set. A further limitation: if you compare month-to-month, you cannot also compare total-to-total. Sorting is also far from trivial, if you want the two December groups to appear before the subsequent January;

(b) Show the comparison month value beside the actual month. In this scenario, the found set will contain only records in the actual range, and the comparison values must be calculated separately (i.e. not part of the native report).

3. How to calculate the comparison values (in scenario (b) above)?

I would avoid using a relationship - because such relationship would have to replicate the actual find (in the given example, it would have to include a predicate for codes < 300). In case of changing the find criteria, you would have to modify not only the script's find steps, but also the relationship's definition.

Instead, I would recommend producing a report for the comparison period first, and using a technique known as Fast Summaries to write all the comparison values to variables. Then produce the "real" report and use a calculation field to get the corresponding value from the variable.

Edited May 25, 2015 by comment

[sandersb]

I had been drifting to the conclusion that I am trying to do something that is possibly best left in the too hard basket.  However your suggestion leaves open a glimmer of hope. I have never heard of Fast Summaries and now I will do some research on the subject. The concept as I conceive it is first to create a separate report for the Previous date range (which is something I should be able to do reasonably easily) and then move from there with calculation fields in the Current date range report to display data  for each previous month extracted from the other report.  Not too sure about that last step but first will see what fast Summaries is all about.  Thanks very much for your guidance