Jump to content

Calc relationship summary


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

Recommended Posts

I'm having problems with one of my calculations.

What I'm trying to do is this:

Database 1 records individual transactions. I have a relationship from Database 2 to Database 1, and that relationship is called "NDirectMutualFunds." I have a calculation field on Database 2 that uses the following calculation:

"Case[NDirectMutualFunds::PayDate >= DateRange1 and NDirectMutualFunds::PayDate <= DateRange2 , Sum[NDirectMutualFunds::AmtExpected],0)]"

NOTE: The brackets in that calculation represent parentheses, but for some reason I couldn't use the parentheses in this post.

My goal is for the calculation -on Database 2- to summarize a particular field -AmtExpected- which resides on Database 1. The trick is that each record on Database 1 contains a date field -PayDate-, and I only want the calculation to summarize those records whose PayDate field falls in a date range which I set. You can see I tried to express that in the case statement at the beginning of the calculation. FYI: DateRange1 and DateRange2 in the calculation are the fields I use to enter the date range.

The calc is so close to working, but it has one little glitch. It only evaluates the PayDate field on the first related record. If, on the first record, the Paydate field is within the date range, then all the records are summarized regardless of whether or not the PayDate fields on the other records are within the date range. If, on the first record, the Paydate field is not within the date range, then the calc returns "0."

How do I get it to evaluate the Paydate field on all the records instead of just the first one?

Please let me know if this doesn't make sense. It's a little difficult to explain.

Link to comment
Share on other sites

In DB1, create a calc field that returns the AmtExpected if the PayDate falls within the correct range (BTW, you can use the "If" statement rather than the "Case" statement, since it's only one "if"). Call this field AmtReallyExpected (or whatever).

In DB2, create a calc field:


That oughta do it.

Link to comment
Share on other sites

Put your calc in Database 1 instead:


Case(PayDate great.gif DateRange1 and PayDate less.gif DateRange2 , AmtExpected)

Then in Database 2, all you need is Sum(NDirectMutualFunds::AmtByDate)

Of course, if your DateRange fields have to be in DB2, it's a little trickier. Are they global fields? If so, you can create a constant relationship to use them in the calc above.

Link to comment
Share on other sites

"I thought I said that."

Looks like our posts crossed while I was pondering the problem. We can't all think as fast as you, Dan. crazy.gif

Link to comment
Share on other sites

I used the technique above for a similar task. Now I would like to do it in a relationship (to show a portal). Is there any way of doing that? When I try it says something about the calc field not being indexable..

Link to comment
Share on other sites

This topic is 8158 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
  • Create New...

Important Information

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