Total of invoices per quarter

[scubart]

first of all I would like to apologize for my english. I'm a Dutchie.

Second; I'm trying to get FM to tell me to total of the taxes on my invoices in all the quarters of this year.

I've been searching and reading and trying but with little succes. I managed to get FM to tell the totals but I can't seem to split this in 4 quarters. I need this because I have to pay my taxes every quarter.

I'm in the progress of migrating my administration from MsAccess to FM (advanced 11).

If you have a sample you can share with me I would be very gratefull.

TIA

Bart

[fseipel]

Try creating a summary field to sum the taxes.

Taxes should be in a field typed as a number. The second layout of attached demonstrates such a layout in a simplified DB containing only Tax and Quarter fields.

If you use a subsummary in the layout, and summarize by quarter, it will present all four quarters in an ordered report.

You may want a function to calculate fiscal quarter based upon date, if needed see http://help.filemaker.com/app/answers/detail/a_id/4833/kw/calculating%20years%20and%20months

Tax_FM.zip

[scubart]

Thanks for your answer and the example! I've found the website with the link and the calculation of the fiscal year was not my problem. It is quite simpel starting january 1st en ending december 31st.

I already created a table with some fields and put a total of taxes_payed in it. This works fine! My problem is/was the view of these totals. I hope I can implement your exampel in my database!

Will let you know!

[scubart]

I had a look at your solution! Very nice but I would prefer my lay-out to filter on quarter based on the date of the invoice rather than putting the quarter in a quarter field ;-)

Your solution is very simple so if I can't find a way to calculate from date-field I might follow your way.

Thanks again!

[fseipel]

In this case, define Quarter field as a calculated field, with calculation = Int((Month ( Invoice_Date )+2)/3) -- this assumes quarters break evenly at 3 month intervals. Invoice_Date = date field containing the invoice date.

[comment]

To avoid grouping records of the same quarter but different years, I'd suggest you use either (result is Text) =

Year (YourDate) & " Q" & Ceiling ( Month ( YourDate ) / 3 )

or (result is Date) =

Date ( 3 * Div ( Month ( YourDate ) - 1 ; 3 ) + 1 ; 1 ; Year ( YourDate ) )

The date result can be custom-formatted to display as "yyyy Qq".

[scubart]

After 1 1/5 years of frustration with FM I'm gonna give it a new try. So used to MsAccess but want a platform independant solution .