Hello,

this is my first post, so be gentle :

I have a problem with date calculation. I have a time period. I need to "group" these days by months and then multiple each group of days with different number. How can I do that?

Days in each "group" (1,2...12) can belong to different years. Am I clear?

Thanks for help!

LinaML

It's not quite clear what you want to achieve, but if it's a summary, where you wish to make the breaker the month - is it just Month("theDate")

Has it something to do with discounts given when sales reaches a certain level inside the same month??

--sd

Hello,

thank you for response.

I need a formula which can calculate taxes for period. Tax per day is different for each month. Is this possible?

LinaML

I'm attaching a sample file.

If the tax rates won't change for each month, then you can create a case statement like I did for the field "Tax Calc"

otherwise you may want to create a table that has a field for the month, the year, and the tax amount. If you would like me to mock this up as well, let me know.

Does that make sense?

test.fp7.zip

Thank you Martha,

but what I need is calculation which will give me the number of days for specific period and then multiple these number with different amount.

Example

Start day: 01/20/2006

End day: 02/17/2006

Number of days: 28

Days - January: 12

Days - February: 16

Result=12*x+16*y etc

Period also can be through 2 years (12/26/2005 - 03/18/2006) or 3 years (12/26/2005 - 06/01/2007)

Am I clear now?

Thanks,

LinaML

MMS6F there's no need to make a long Case( statement, when dealing with integers, Choose( does it faster:

http://www.filemaker.com/help/FunctionsRef-33.html

When I saw the word "group" did I think of Summaries, so if there are SEVERAL earnings (more records) within the same month is this function an option:

http://www.filemaker.com/help/FunctionsRef-341.html

Say you wish to deal with sales discounts, dependant on the month could it be done this way:

Let(theGrouping = GetSummary ( SaleSumming ; makeBreaker );





Case(

theGrouping < 1000; Sales ;

theGrouping < 2000; Sales * ,8;

theGrouping < 3000; Sales * ,7;

theGrouping < 15000; Sales * ,6;

))

What it does is that it works through the lines, first time a "Yes" occures in the Case( statement wil the belonging calc work, since filemaker not as c++ deals with "switch".

--sd

Update! I do now see clearer what you're after. This is definately Summaries territory, make a found set by searching on the start date and the end date.

Choose ( makeBreaker-1; ,9; ,8 ; ,7; ,6; ,6; ,7; ,9; ,8 ; ,7; ,6; ,6; ,7 ) * GetSummary ( SaleSumming ; makeBreaker )

...is then the field that's summarised (yet another summary) on to get the payout for the entire span! makeBreaker is a calc' = Month(theDate)

--sd

'''

Edited May 24, 200619 yr by Guest

Seems like a tough problem. It wouldn't be too hard to calculate:

- the number of days falling in the particular month in the starting year;

- the number of days falling in the particular month in the ending year;

- the number of whole years between starting year and ending year.

But if the particular month is February, we'd need to check each year for leap/regular.

I would rather use a 'brute force' calculation: a custom function that loops thru the days one by one. I haven't checked this very thoroughly, but it seems to work (slowly, though):

CountMonthDays ( startDate; endDate; monthNumber )

Let (

d = Month ( startDate ) = monthNumber

;

d + Case ( startDate < endDate ; CountMonthDays ( startDate + 1 ; endDate ; monthNumber ) )

)

Hi comment

not so slowly...< 1 sec for 25 years !

BTW: Beautifull calc but...can you explain for us mere mortals !

( I think true (1) + recursion : is it correct ?)

Ah. I did this in a file with some previous CFs dealing with date ranges, so it seemed slow, because all the other functions were calculating at the same time.

Your explanation is correct: test returns 1 if the date falls in the specified month; then move on to the next date, adding up as you go.

I was thinking it could be optimized by jumping, Edoshin-style, straight to the next 1st of the month. So say for 25 years, instead of evaluating app. 365*25=9125 days, it would only require 25 iterations or so.

Hello!

Thank you all! I finally did it with my own calculation (if and case functions), but I will try yours.

Thank you very much!

LinaML