Jump to content

easy answer?


 Share

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

Recommended Posts

I'm trying to devise a script/calculation that will look at 8 date fields (DO1, DO2 etc) and if the value is 1/1/02...12/31/02, a value in a corresponding number field (AO1, AO2 etc) will be added to a total figure. In other words, there are 8 quarterly payments scheduled over 24 months, but I want to get a total of just the payments that will fall in a certain date range. It seems as if it should be simple, but the only thought I have requires a script with 8 different subscripts.

Suggestions appreciated.

Chuck

Link to comment
Share on other sites

Easy answer: calculated fields... wrong forum, oh well, here goes:

Make a calculated number that =

((Year(D01) = 2002) * A01) +

((Year(D02) = 2002) * A02) +

((Year(D03) = 2002) * A03) +

etc.

In other words, the first part of the expression yields a 1 (true) or 0 (false), then multiply by the field and add it up!

If you want to make it more flexible, replace "2002" with a number field, e.g. MyYear, or an expression that evaluates start and end dates. Think about it and if you still need help let me know.

Link to comment
Share on other sites

Hey, that's great! Thank you. It works like a charm. I did run in to trouble when I tried setting up a more flexible field (YearToUse). The year has to be entered on every record I want to sum, which I can do quickly using the Replace function. It should probably be a Global Field. I haven't worked with Global Fields, but I guess now's a good time to start.

Thanks again,

Chuck

Link to comment
Share on other sites

You're right, I should have specified global fields, that's definitely the way to go.

Like this:

((D01 great.gif globalStartDate and D01 less.gif globalEndDate) * A01) +

((D02 great.gif globalStartDate and D02 less.gif globalEndDate) * A02) +

((D03 great.gif globalStartDate and D03 less.gif globalEndDate) * A03) +

etc.

Link to comment
Share on other sites

This topic is 7194 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.