Calculation difference in same field but previous record

[bmnation]

I'm am trying to set up a calculation field that will calculate the difference between the current and previous record of the same field, such as the difference between the number of people who attended this event vs the number who attended the previous event. How do I specify a field in this record vs the same field in the previous or for that matter, any two records? Any suggestions?

Thanks in Advance.

Brad

[Raybaudi]

If the meaning of "previous" record is based on the position that a record can have in a list of found records:

Case(

Get ( RecordNumber ) > 1 ; YourField - GetNthRecord ( YourField ; Get ( RecordNumber ) - 1 )

)

The storage option of this calculation must be: Unstored

[comment]

There are two ways to do this:

1. Use the GetNthRecord() function to get data from previous record: note that this depends on the current found set and sort order;

2. Define a relationship to the previous record; this ignores the found set and sort order - and if the related record is deleted, the relationship will not switch to another.

[Miss A!]

 

Hi there, 

 

I seem to be caught up with a similar challenge.....

 

@Comment,

How do i define a relationship between previous and next record, in fact how do i do that(define relationship) between related records existing in the same table? Further to that i would then like to calculate the difference on field values of this related records.

 

[MikeKD]

OK, I'm just guessing here, but could you create a field something like PreviousFieldID.

Then, when creating a new record, either automatically enter the ID of the previously created record, or have a value list to choose the right record.

To relate the two, I suppose you'd have to create a new TO and link this new field with the ID of the original record.

 

Please don't try this until you've had confirmation from someone who actually knows what they're talking about that it could work!!!

[Miss A!]

I wouldn't even know where to start if i had to try it.......What i have is multiple records with same Serial but different entry/recording dates. I would like to calculate the difference between a price field value of a record created previous year to the price field of a duplicate record created this year - some one walk me through this please if it is by anyhow a feasible exercise.

[MikeKD]

What i have is multiple records with same Serial but different entry/recording dates.

Assuming that the Serial is the primary key, I'd have thought that every record should have a different primary key.

Otherwise what you've got is two versions of the same record, but with slightly different data on them..

[Miss A!]

 

Yes Mike, that is right - is there any workaround to achieve my desired result?

[eos]

some one walk me through this please

 

Miss A –

 

the basic idea is to think in terms of records, rather than fields.

 

If you have a Bookings (?) table with fields for year, season, and price, and a foreign key like lodge/roomID etc. (the entity that is being booked/rented), then you could either set up a relationship where

 

Bookings::season = Bookings_selfPrevious::season

Bookings::lodgeID = Bookings_selfPrevious::lodgeID

Bookings::year > Bookings_selfPrevious::year

 

and sort it by year, descending; then Bookings_selfPrevious::price would give you (from the context of Bookings) the price entered for the same season in a booking of the *latest* previous year. (i.e. you would jump gaps)

 

or create a calculation field cPreviousYear = year - 1, and define the relationship as

 

Bookings::cPreviousYear = Bookings_selfPrevious::year

Bookings::season = Bookings_selfPrevious::season

Bookings::lodgeID = Bookings_selfPrevious::lodgeID

 

where Bookings_selfPrevious::price will only return a result if there is a record for the last year.

[eos]

Assuming that the Serial is the primary key, I'd have thought that every record should have a different primary key.

Otherwise what you've got is two versions of the same record, but with slightly different data on them..

 

The booking table (if that what it is) would have its own primary key, but two bookings in two different years, but for the same room would have the same foreign roomID – and it's these records* you want to compare.

 

* which also share a season, of course; somehow I couldn't fit that factoid into the sentence proper  without altering its meaning (since it doesn't impact the roomID) …

[Miss A!]

 

Thank you eos - *slowlilly* am getting somewhere.......

[Miss A!]

 

Totally got it, needed to separate value list field Season_Year(2015 HS, 2015 LS etc) to be lxt_Season(LS and HS) and create separate field for the year which is typed lxt_Year(2013, 2014, 2015....etc).........Went on to create the relationships as suggested and Voila 0/ 0/ 0/

 

 

Thank you so much.....

 

[eos]

Totally got it … and Voila 0/ 0/ 0/

 

Glad to hear it! You've discovered the “fun” part in programming …