Compare Values

[stanB]

I market & wholesale cars & trucks for rental companies. I have a table for the available vehicles and a table where I store weekly values from a market guide. These values vary according to mileage. This is how I get the current information into my Vehicle table for the current week:

 

Last(Case ( Miles ≥ 1 and Miles

 

I need to find the next to last value (the week before) and calculate how much the value has changed from week to week. I have hit the wall on trying to figure this out. Do I need a join table?

 

Thanks for the assistance.

[AHunter3]

 

Last(Case ( Miles ≥ 1 and Miles

 

 

This formula indicates that you have a single related BBCode record which has values in fields a1_10, a10_15, a15_20, etc; that's not really a good design. You'd be better off with a relationship to BBCode based not only on whatever it is based on now (vehicle ID and Week?) but, rather, that plus the additional match of Miles being ≥ BBCode::Min and Miles being ≤ BBCode::Max. You'd have different BBCode records for each individual value instead of each record containing a batch of values.

 

But that doesn't answer your question.

 

 

To answer your question, I need to know how the relationship to BBCode is made to be week-specific. I can think of several possible strategies you might have used:

 

• You might be sorting the relationship itself by creation date in descending order, so that when you add a new batch of records from your market guide, the new ones become the ones that the relationship "sees".

 

or

 

• You might be using a Week StartDate field in BBCode and a calculation field in your Vehicle table that gets the week number for the current date, and be using that field combo in your relationship.

 

or

 

• You might be using a StartDate and an EndDate pair of fields in BBCode and a calculation field in your Vehicle table that is set to Get(CurrentDate), and be using CurrDate ≥ StartDate AND CurrDate ≤ EndDate

 

 

At any rate, what you need is to set up a second relationship to BBCode, which will require a second table occurrence of BBCode, let's call it BBCode_wkAgo. How you would set it up would be dependent on how you've set up the original relationship to snag the current week's value. If, for example, you're using the third method I described above, you'd create a second calc field in Vehicle, let's call it WeekAgo, defined as Get(CurrentDate)-7, and relate it the sameway you related CurrDate in the original relationship. Reaching through this new relationship you get the values for 1 week ago, assuming that you want the market values from last week as they would apply to this week's miles.

 

If, instead, you're trying to compare the value of this week's miles at this week's price to last week's miles at last week's price, you're in a different situation. I'd need to know how your field called Miles gets its value. If you're overwriting it each week, you can't do it (you've erased last week's values); if it is a calcfield, presumably it is a calc that references another (so far unmentioned) table containing dates and miles and the calcfield sums them up. If that's what you have, you'd again create a corresponding relationship that reaches into that table and snags the miles that were applicable to that vehicle the previous week.

[stanB]

I enter new values every week on Monday or Tuesday. bookDate is the day I enter values. Here is a screen shot:

 

 

 

I just use the Last function to get the last record entered and the Case formula to get the correct value according to the Miles field in the vehicle record for three different fields ( cBook, abook, & rbook)

 

I have values for about three months in the table. I need to calculate the change in the newest value and the next oldest for all three fields. ( ie 1/18/16 value to 1/11/16 value)

 

Thanks

BBCode.pdf