Jump to content

max function


faaslave
 Share

This topic is 5798 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

This topic is 5798 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.