Hello, i've a challenge. Maybe someone can help.
I want to have a calculation like this:
Count days (In month (variable like January, February, etc)) for (Day (variable like monday, tuesday, etc)).
You'll need the year, too.
That can be made with a recursive Custom Function, with a looping script, with a repeating calculation field or even with an unstored calculation.
I like the last way.
So you have 2 number fields ( year and month ) and 1 text field ( DayName ), let give them the names: Y , M and DN
Create a calculation field, name it "result", do not insert any calculation inside it and exit.
Now re-open the calculation editor for that field, insert this calc:
Let([$i = $i + 1 ;D = Date ( M ; $i ; Y )];( DayName ( D ) = DN ) + If ( Month ( D + 1 ) = M ; result ; Let ( $i = "" ; "" ) ))
and make this calculation UNSTORED.
Note that you could see some comments on this type of calculation and an example on: http://www.fmforums.com/forum/showtopic.php?tid/216662
Great! It's working! i don't now how it works but it works ;).
Now i've a another challenge. The results (of a week) must be multiply with number of hours per day.
Y M DN Result Hours per day Total
2010 9 Monday 4 1 4
2010 9 Tuesday 4 8 32
2010 9 Wednesday 5 2 10
And then for a whole week in one calculation...
Result: Grand total for the month: 46 (that's the number i need).
How can i do this? Y and M are variables and DN is always permanent. Hours per day are fixed by client.
I do not understand the structure of your DB.
one record ?
If so, a simple summary field ( Total of Total ) will do the job on the found set.
The data is of a database Contract and a database Invoice.
Hours Monday from [Time] Monday to [Time] Total Monday [Number (Hours per day)]
Hours Tuesday from [Time] Tuesday to [Time] Total Tuesday [Number (Hours per day)]
Calculation: [Number (Hours per day) for Monday (if completed...)] * Mondays in Month in Year + [Number (Hours per day) for Tuesday (if completed...)] * Tuesdays in Month in Year + etc. until Saturday.
The result is total hours according Invoice for that month in that year.
Can you help?
Yes, but I need to see your DB, even a clone.
I've done it!
Thanks for the help!
Retrieving data ...