Calculating percentage from many entries

[zab]

Hello

I have a new problem to solve and I'm not to sure how.

I have a table that shows Percentage of accomplishment for a type of job. The same job can be accomplished over several weeks and I need to find the percentage between the two last entries.

example of the data entry

Date-------- Job %

28-05-2010 15%

04-06-2010 75%

18-06-2010 100%

How can I calculate the difference in percentage from the last two entries (result being 25%) and ignore the previous week(s)? Keep in mind that there can be several weeks between two entry.

I'd prefer to have a calculation field for this but it can also be integrated into a script. I will use this data to build a report (starting from another table).

Thank you.

[comment]

Try =

Let ( [

n = Count ( Percentages::JobID ) 

] ;

Last ( Percentages::Job% ) - Case ( n > 1 ; GetNthRecord ( Percentages::Job% ; n -  1 ) )

)

This assumes that entries are in chronological order.

[zab]

Hello

I can't get it to work.

I made a small db for you to try... again :

PercentDB.fp7

Thank you

[comment]

I thought this was being calculated from the Jobs table?

[zab]

No I can't. I'm also using a portal to show only the related record. Any other idea how I can get this percentage?

[comment]

I don't understand. The "last two entries" are obviously the last two entries for the SAME job, so the difference between them is an attribute of job. Don't see what the portal has to do with it.

[zab]

You're right. I was just mentioning it in case it could help in some way. But it's just the same.

[comment]

So did you get it to work?

[zab]

No I'm trying again right now. You know I will have to transfer that percentage in my Report table so I guess I could do it in a script as well.

[zab]

I did it with a script.

[comment]

Why?

PercentDB.zip

[zab]

Now explain to me why your calculation works if put in another table and why it doesn't from the percentage table itself?

[comment]

The Last() function works over a relationship, not found set (and it would have a hard time to pick the last record of each job in the found set). Similarly, GetNthRecord uses the relationship to filter out records of other jobs, so the last-but-one record is job-specific.

It's possible to calculate the same thing in the Percentages table, using a different formula. But it would be correct only when records are sorted by job and by date.

[zab]

Thank you I'll try to remember that.

btw I use a portal to enter the percentage and it is sorted by job# and date.

Anyway it work just fine with the script and the variables. Maybe even better because I feel I have a better control over the dates. So I will leave it that way. It's more convenient.

Thank you for your help.