A complex calculation

[edudna]

In my educational database I have to extract in a teachers point system how many points they earn depending on the years of employment and kind of schools. The mathematical type is:

Date of the employment end-Date of the employment begin plus (x)points of the particular school (coeficient z)

is like

Date(end)-Date(begin)x (z)=

The problem is to calculate the days that exist between dates. Then z will be translated easily to numbers and multiply. Any ideas please. I think that is interesting.

Pascal

[Raybaudi]

Hi

it is not so complex !

A date is a number, so EndDate - StartDate gives you the number of days between those dates.

So the calc is:

(EndDate - StartDate)*z

[edudna]

Thank you for your reply,

If you want tell me how can I change the number of days that I finally found in a format like:

xyears, ymonths, zdays

[Raybaudi]

Haven't you said that you had StartDate and EndDate ?

[edudna]

Yes I have said that. Let me explain a little more.

We have to multiply the difference between endDate and StarDate with the coeficient z but I have to say that coeficient z is not so simple.

It counts the days for a whole month when days are >15

If we have a difference that is 167 days the coeficient counts and multiply six months because 167=5months and 17 days>15. If we have 163 days counts 5 months only because 163=5months and 13 days<15.

Pascal

[comment]

Round ( days / 30 ; 0 ) ??

[edudna]

I see that...

Thank you for your response. FM does almost anything I think.

Pascal

[Raybaudi]

So the final calc becomes:

Round ( ( EndDate - StartDate )/30 ; 0 )*z

[edudna]

Reply to myself... funny.

So the whole relation is

(Round((EndDate-StartDate)/30; 0)) * z/12

but z can be

A,B,C,D,E,F,G,H,I,J and it must be translated into numbers

A=1

B=2

C=3

D=4

E=5

F=6

G=7

H=8

I=9

J=10

That is all. Any ideas please or I will try myself I think I will make it...

Pascal

[Raybaudi]

Why z/12 ?

What is 12 ?

[edudna]

z is a coefficient for a school year but almost never a teacher can have a full year to a particular school. So we have to find how many points he or she has to an amount of months.

12 are months.

Pascal

[Raybaudi]

Round (( EndDate - StartDate )/30; 0 ) * Position ( "ABCDEFGHIJKL" ; z ; 1 ; 1 )/12

[edudna]

Thank you for your calculation but returns zero. I think something I have missed but this I have done it works perfect:

Let(z=(Case

(CATEGORYOFSCHOOL ="Α"; "1";

CATEGORYOFSCHOOL ="Β"; "2";

CATEGORYOFSCHOOL ="C"; "3";

CATEGORYOFSCHOOL ="D"; "4";

CATEGORYOFSCHOOL ="Ε"; "5";

CATEGORYOFSCHOOL ="F"; "6";

CATEGORYOFSCHOOL ="G"; "7";

CATEGORYOFSCHOOL ="Η"; "8";

CATEGORYOFSCHOOL ="I"; "9";

CATEGORYOFSCHOOL ="J"; "10"

));

(Round ((EndDate-StartDate)/30; 0)) * z/12)

[Raybaudi]

So now try this:

Round (( EndDate - StartDate )/30; 0 ) * Position ( "ABCDEFGHIJKL" ; CATEGORYOFSCHOOL ; 1 ; 1 )/12

[edudna]

Thanks It works fine now, When I put the appropriate fields. But your calculation have a weakness if it is finally weakness. How does it work if a category of school is indicated by two letters for example "ST"?

Pascal

[Raybaudi]

It will work the same way of your calculation ! :)

[edudna]

Round (( EndDate - StartDate )/30; 0 ) * Position ( "ABCDE "ST" FGHIJKL" ; CATEGORYOFSCHOOL ; 1 ; 1 )/12

What can we do if "ST" is between E and F. It returns one digit above here... If you have time write to me the whole calculation with "ST" assumption.

Pascal

[LaRetta]

but z can be A,B,C,D,E,F,G,H,I,J and it must be translated into numbers

How does it work if a category of school is indicated by two letters for example "ST"?

You can NOT expect someone to provide a solution when the examples you provide are inadequate. You should be using a table to hold the values for various schools. We keep coming back to proper relational structure, don't we?

The letters are the SCHOOLS and the numbers are the unique serial IDs we keep talking about.

LaRetta

[edudna]

Schools are ten kinds nothing more:

A=1 POINT

B=2 POINT

C=3 "

D=4 "

E=5 "

ST=6 "

F=7 "

G=8 "

H=9 "

I=10 "

Pascal

[comment]

... Position ( "ABCDESFGHI" ; Left ( CATEGORYOFSCHOOL ; 1 ) ; 1 ; 1 ) ...

[mr_vodka]

edudna,

Not to get on you, but I have noticed a trend with your posts. You tend to post something, and then after someone offers you a solution, you post with well what about this case, and what about that case. If you had given us the entire description from the beginning of how you have it setup and what exaactly you are trying to achieve, you would get suggestions to your problem sooner; rather than this back and forth stuff.

I also agree with LaRetta here. If you had another table to store your coefficient codes with their corresponding points, then you wouldnt even neeed the Position() function. You could just reference that related points field. Will there ever be more than the 10?

[edudna]

I have tried many times to give the whole problem from the beggining. Nobody answered. But as a phd in Education (not in FM) I know that you better understand if information is in pieces and have a logical ierarhy. As you see we now have described elaborately the problem and give all the possible solutions. Other novices and beginners will read this discussion you can not deny this. Always also I appreciate your help and I am lucky that I find this blessing forum!

Pascal

[mr_vodka]

Well, that may be true in teaching TO someone, but then you should well know that in order to help someone the best that you can, you need to assess the situation by finding out as much information as possible to help the student. For example, if I went into a doctor's office and said my stomach hurts, and just that, it would take a lot longer to find what may be the cause. However, if I went into the doctor's office and said my stomach hurts but told the doctor that I did drink a gallon of milk, I did eat spoiled meat, or I swallowed a metal piece. This changes the situation completely and his solution to rectify each situation or treating it as a cold would be completely different.

Your original question of asking the simple how to get the date difference times z:

Date(end)-Date(begin)x (z)

is Much different from Date(end)-Date(begin)x z

where z can be A,B,C,D,E,ST,F,G,H,I,J,K,L and each one of those has a value associated with it.

[Søren Dyhr]

I can't urge you enough to read this (again)

http://www.sciam.com/print_version.cfm?articleID=00010347-101C-14C1-8F9E83414B7F4945

I really agree with John here, educaters are too used to make students interpolate and syntezise knowledge ...we're more roaming here to optain balanced chains of diagnositcs than being thrown into the deep end of the pool. We kind of can swim already, but would like to do it gracefully - with the students is it the other way round ...they need the educater to teach them swim.

--sd

[Genx]

Interesting read anyway Søren

[edudna]

I also aggree with you. You can never become educators or teachers you remain FM experts or you attend to be like that. Education means patience and reflexibility. I have never been taught FM from anyone I am learnig of my own. You are experts but not good teachers. Why you should be?

As for Soren's article. Gognitive phychology change like cars in a highway my friend. It never be the same. Now chaos theory suggest us to look for pre-existence knowledge in our brain. Systems of knowledge are chaotic not stable.

I am not expert in FM. Do you feel expert in education?

Pascal

[mr_vodka]

I have never been taught FM from anyone I am learnig of my own. You are experts but not good teachers. Why you should be?

And neither has over 90% of this board. Most of us have never been formally trained in FM. We all started where you did; self teaching ourselves and "learning" from others mistakes and taking advice from those who had more experience than us. As for not being "good teachers" it is hard for anyone to do the instruction when we can not fully identify the problem. That is like saying here are the the following: 2, 8, 3, 6, *, +, / Now help me figure out the answer. Well how can we give you an answer without knowing that really you wanted it to be like this (2+8)* 3 / 6 which is a lot different from 2 + 8*3 /6

I am not expert in FM. Do you feel expert in education?

Pascal

You do not have to be an "Expert" in education to know that the more information you reveal about a situation will allow others to help you better.

[Søren Dyhr]

I have never been taught FM from anyone I am learnig of my own

Try to teach someone filemaker here for a change, and notice on your own, how wexing the omni-absent "mission statement" really is!

The way a mission statement generally is written down, is to avoid any verbs in past tense or presence, only futur works - words like should and would.

Education means patience and reflexibility.

Yes ideally, but as such can it never be cleaned entirely from political intriques, pride and ambushed feelings. Think of the jesuitic struggle against the heliocentric view of the world(s), their task have always been to keep things hypothetical - never give in.

Think of the master of the game's fate in:

http://en.wikipedia.org/wiki/The_Glass_Bead_Game

Perhaps the reading of this:

http://www.pokerplayermagazine.co.uk/pokertips/skillsandstrategy/347/insideedge_basic_holdem_strategy.html

....has more to do with it, than we wish to know?? In business schools teachings of marketing is just worshipping of Edward de Bono instead.

This urge to keep stuff as hypothetical as posible, have a lot of similarities to this thread, where I admitted - sometimes were carried a little away:

http://www.fmforums.com/forum/showtopic.php?tid/177813/hl/parot/tp/1/

--sd

Edited January 23, 2007 by Guest

[edudna]

I think that we become FM Philosophers in fact. Never mind.

Gentlemen I wish good jobs to everyone and I hope a big company hire you for an interesting project.

Pascal