max function

[faaslave]

If I have a list of values, lets say:

100

125

180

300

340

378

and I use the max (field)

I get 378

max (field) -1 =377

What I want is the second highest value 340

Is there a way to say find the max, then back it off one occurance?

Thanks Dave

[comment]

You could substitute out the first max, then use Max() again on the result. Mind that if two values are at max, you will get the third.

In general, if you need to summarize/report on multiple values, it's not a good idea to put them in a single field.

[faaslave]

I should have explaine more, this is what I am trying to do.

I use the max function to find the most recent date in the payperiod table. Once I have that, I grab the dues amount that matches the date I have. This way I can always find the most recent dues you paid me.

What I need are some reports that show changes in dues. In order to do that I need to find the second most recent date in the payperiod table.

Then when I run a report it will see if there is any change in the dues from last payperiod, and let me know.

Maybe you quit paying, or this is your first payment, or the amount changed. I need to know these things.

Thanks for your reply,

Dave

[faaslave]

Well I am still having trouble.

This calc gets the most current date I need:

Max ( DuesNPAC_getdate::Date )

It returns 11/7/2006

This is the calc I tried to use to change that date to an earlier date.

Replace (

Right ( Max ( DuesNPAC_getdate::Date ) ; 4 ) ;

Right ( Max ( DuesNPAC_getdate::Date ) ; 4 ) ;

4 ;

1980 )

I was hoping this would return: 11/7/1980

Then I would need a way in the same calc to grab the new max date, which would be the second most recent date.

Maybe with a let function with the first calc, then use the max function to grab the new max date.

I tried the substitute but replace seemed easier for me. I guess neither one was easy for me. This is way over my head.

I am not sure that these functions will work with a date field. It says that it returns text in the help file. There has to be a way.

Help, Dave

Edited November 20, 2006 by Guest

[comment]

Ah, so you are talking about RELATED records. OK, then: assuming that (1) you have not defined your relationship to sort otherwise, and that (2) your dates match the creation order, and that (3) there are no duplicate dates, you can get the last-but-one date by:

GetNthRecord ( DuesNPAC_getdate::Date ; ValueCount ( List ( DuesNPAC_getdate::Date ) ) - 1 )

[faaslave]

Sorry, I should have made that clear.

I am always amazed how you come up with these calculations. It's quite impressive.

I tried what you said and it still retirned the most current date. I know it's hard to help with no file. I made a clone, because of the sensitive info. I wish I could have left the data on.

The DuesNPAC_getdate table from Members is where I am pulling the data.

Then the DuesNPAC_most_recent path to grab the most recent dues

and DuesNPAC_second_most to get the second most recent.

Maybe my logic is wrong? The most recent one works because I use it to calculate salaries.

Thanks for your time, Dave

FAAMA_1.51test_Clone.fp7.zip

[comment]

Let's take a simpler example.

LastButOne.fp7.zip

Edited November 21, 2006 by Guest
Changed the attached file - see below why

[faaslave]

Well your example works perfectly, so it has to be something to do with my relationship. I am trying to figure that one out.

Thank you for the calc. I will be using it as soon as I find my relationship error.

Thanks, Dave

[comment]

Perhaps check again the three conditions I have mentioned earlier.

[faaslave]

I think it is a problem with one of the first two.

I noticed two things

If I took the max off, to get the most current date, the result was the second most recent date. So I assume they are sorted wrong or they were created out of order.

I checked the kp number, and they were in order since the payperiod list for the year is created with a script.

But the importing of data into the payperiod is done in any order.

I am still trying to figure it out.

Thanks Dave

[comment]

If you are not sure of the proper order, then define the relationship to sort by the date.

P.S. I have just noticed your profile says v.8 - which does not have the List() function. But you said my example worked, so you must be on 8.5?

[faaslave]

I will have to change that, I have 8.5.

I have been trying,

When I change the relationship to sort with date (descending), no luck. I am still playing with it, but that should have worked.

I changed the calc to -2, and a different date came up, the oldest.

So I am sure they are out of order. How, I have no idea.

It is a puzzle

Dave

[faaslave]

If I make the sort acsending, then change the calc to -3, I get the correct date. I know that isn't going to work, but it proves the sort is not working as expected.

What a mess.

Thanks, Dave

[comment]

Random experimenting is not going to lead to any meaningful conclusion. Try taking the calc apart, step by step. First, use only this part:

List ( DuesNPAC_getdate::Date )

Make the result Text, and make the field tall enough to see the entire list (you should see a list of all related dates, sorted in the same order as you have defined for the relationship).

[faaslave]

I will do that.

I apprecriate all your help. And I wan to understand why this doesn't work.

I just got back from the doctor's office, where my wife had an exam. While I was in the waiting room pondering my dilema, the light bulb clicked on.

None of this is really necessary.

I run a report for a payperiod to show members that have had a change in their dues from last payperiod.

Well lets see, that means I know the date of the payperiod.

And that date -14 gives me the last payperiod date.

Once I have these figures, a simple calc should retrieve the dues I need.

I am about to work on this. I hope that's it. Sometimes I think I need to slow down and think, is there an easier way to do this?

Even if I didn't need the other way, which isn't for sure yet, it is a great learning experience. I will of course be up at nights until I have both ways solved.

As big as my databases are getting, and I have two, I think it is time to learn about repeating fields. I avoid them like the plague, since I don't really understand their usefulness.

Thanks, Dave

[comment]

Sometimes I think I need to slow down and think, is there an easier way to do this?

Well, you are not the only one. I have just noticed I have made this twice as complex as it needs to be. I don't think that's the source of your problem, but there really is no need to use List() where a simple count will do the job:

GetNthRecord ( Child::Date ; Count ( Child::Date ) - 1 )