Running total problem...

[GIScrew]

Thanks Ido and Brian,
I am still having a problem. I should explain a few factors. I have 4 tables linked by a primary id, the census tract id number. My primary table is the assessment area table, most important, composed of 256 records, ie 256 census tracts for this assessment area, all unique census tract ids. My other tables are loans, denials and deposits. I want to display number of indate loans in one field, sum of the indate loans in the next field, deposits, sum of deposits, all for each census tract id in the assessment area. The problem i am running into is the loans, denials, deposit tables include duplicate census tract ids. I grouped the census ids in for the assessment area. I applied both solutions, running total by count type and the summarizing based on 1 or 0. The results are 1s and 2s for each census tract id. I am a small company looking to go big. I'm close, but where lies the problem?
Thanks,
GIScrew

 

[lbass]

You should reply in the box at the bottom of the thread rather than starting a new thread. I think you should supply some sample data so we can visualize the problem, and then this should be easily solved. On the surface, it looks like you could group on {assess.ctID}, and then for the count of indate loans, use the running total editor:
Choose {loan.loanID}, distinctcount, evaluate based on a formula:

{table.indate} = "Y" //gathered from your other post

Reset on change of group (census tract ID).

For the sum of the loans, change the summary to sum. For the sum, you might also need to change the evaluation formula to:

{table.indate} = "Y" and
{loan.loanID} <> previous({loan.loanID})

...assuming that records are sorted or grouped by loan ID. The second clause should prevent duplicate amounts from contributing to the sum.

You should be able to make similar running totals for the other summaries you want. This should at least get you started. For further help, you should provide sample data.

-LB

 

[GIScrew]

Thanks LB,
I grouped the tracts for the assessment area. I attempted your formuals, so far didn't get any errors but it only returned 1 loan for one of the tracts and gave me the balance for the loan. Here is an example of the data.

Table 1 assess_area
tct_id Income_level
1.01 Middle
1.02 Moderate
2.01 Low
2.02 Upper

//etc, etc

Table 2 loans
tct_id loan_name indate balance
1.01 frank Y 10,000
1.01 fred
1.01 dave Y 20,000
1.02 Jeff Y 15,000
2.01 Greg 10,000
2.01 Alex Y 12,000
//etc, etc

-The deposit table is identical to the loan table and contains duplicate census tracts. The tables contain more fields but are irrelevant. Could I not have the tables linked correctly? The tables are linked based on a unique id composed of the state id, county id and census tract id which makes the id unique, and this id field is in each table. I am somewhat new to crystal, have just done simple reporting in the past. I'm not sure if this what you wanted for a sample of the data. Let me what you think.
Thanks,
GIScrew

 

[lbass]

The running totals should be placed in the group footer. Are you doing that?

-LB

 

[GIScrew]

LB,
Thanks, placing the running totals in the footer helped. My loan amount sum is populated with numbers but the numbers are wrong. The indate loan count is populated but if there are any indate loans the value is a 1 and not the actual count. If there are no indate loans, there is a 0, which is great. I appreciate the help. Let me know when you get a chance.
Thanks,
GIScrew

 

[lbass]

I think you must be setting up the running totals wrong. In your example above, to get a running total count of indate loans by census tract ID, you should be choosing {table2.censustractID}, count, evaluate based on a formula:

{table.indate} = &quot;Y&quot;

Reset on change of group (censustractID).

For your sum, you would choose {table2.balance}, sum, evaluate based on a formula:

{table.indate} = &quot;Y&quot;

Reset on change of group (censustractID}.

Both of these running totals would be placed in the Group (censustractID) footer.

This should return the following for your Table 2 data above:

tct_id count total
1.01 2 30,000
1.02 1 15,000
2.01 1 12,000

-LB

 

[GIScrew]

LB,
Thanks. I was setting up the running totals wrong. All of my count/sums are working properly. I am having only one small problem. After I placed my loan count and loan sum on the report a couple of census tracts in the census tract field disapeared. For example, hamilton county, census tracts 3.01 and 3.02 in the cenus id field disapeared. There are no loans for these tracts but other census ids have no loans but are showing up as 0's. The tracts are displayed when the loan count and sum are not on the report. So far it only happens to those census ids. Could it be within my report options or a setting?
Thanks,
GIScrew

 

[dgillz]

Please post your record selection formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[GIScrew]

As you can read from the posts above I have 4 tables. I need to display all of the census ids from the assessment area table. Then I have running totals for the count of indate loans, and sum of original balance for each census id. All of the census ids apppear until I place the count/sum runnnig totals on the report. I don't have a record selection for my assessment area. The assessment area database was created in a geographic information system software, therefore there is a census id for each census tract, no duplicates. Should I group the census ids for the assessment area table even though there are no duplicate census ids?
Thanks,
GIScrew

 

[GIScrew]

One other small problem. When I create running totals for the count of deposits and sum of deposits, they work properly until I create running totals for the count of total loans, indate and non indate, as well as the sum of total loans. When I place the loan running totals on the page, the deposits count ends up multiplied by the count of loans for the census id. The loans and deposits are seperate tables. When I remove the deposits from the report the count for loans returns to the correct value. Any ideas?
Thanks,
GIScrew

 

[lbass]

How are your tables joined? You should have a left join from assessment area to loans and a left join from assessment area to deposits.

Please provide a sample of your report layout and results when you include loans and deposits, along with what the results should be. I suspect you need to change the reset on your running totals. If you had a layout like this, where deposit was 500 and 300, and loan amounts were 1000 and 400 then to get the following results:

ID depID loanID deposit dep rt loan loan rt
101 23 68 500 500 1000 1000
101 23 75 500 500 400 1400
101 35 68 300 800 1000 1000
101 35 75 300 800 400 1400

...you would have to reset the dep rt on change of depID, and reset the the loan rt on change of dep ID as well.

-LB

 

[GIScrew]

Here is a sample of my report layout.

ID DepCount DepBal LoanCount LoanBal Loan/Deposit Ratio
101 3 478 5 500 104.6
102 2 320 2 500 156.2
103 3 900 3 800 88.88
104 3 1050 4 800 76.1

-The loan/Deposit ratio is the loanbal/depbal for each census id. The deposit numbers are correct until I add the loan count and loanbal to the report. For example when I add the loan count to the report it multiplies the depcount by the loancount for each census id. The first id in my report is 102.3. The depcount is 133. When the I add the loan count to the report, the depcount turns into 931 due to the loancount being 7. 7 is the correct loan count. Any ideas?

The left join of the tables corrected the disapearring census tract problem.
Thanks,
GIScrew

 

[lbass]

You are showing the group level display of your report. You need to take a look at the detail section. Try adding the running totals to the detail section so that you can see what each running total is counting--then the necessary changes to the reset should be obvious.

Otherwise, please provide a sample of the detail level of the report, which probably looks something like my example above. You should also add whether you have IDs specific to each deposit and each loan transaction, since you will probably need them for resetting your running totals.

-LB

 

[GIScrew]

I added the running totals to the details section and the loan count is the same value as the depcount at the last census id for the group The details layout looks similar to the example below:

id DepCount DepBal LoanCount LoanBal
101 1 500 1 500
101 2 600 2 600
101 3 300 3 300
102 4 400 4 400
103 5 600 5 600
104 6 800 6 800
105 7 900 7 900
105 8 100 8 100

-The depcount and depbal are correct until I add the loancount and loanbal to the details section, similar to the example above. This is my last problem I have, all of your other solutions have worked perfectly. I appreciate all of the help.
Thanks,
GIScrew

 

[GIScrew]

**************************************************
Correction, the depcount resets after the id changes
**************************************************
id DepCount DepBal LoanCount LoanBal
101 1 500 1 500
101 2 600 2 600
101 3 300 3 300
102 1 400 1 400
103 1 600 1 600
104 1 800 1 800
105 1 900 1 900
105 2 100 2 100

That could change your answer, or possible solution.

-GIScrew

 

[lbass]

It doesn't make sense that the loan and deposit amounts would be the same. I don't need to see your running totals, I need to see the display of the fields that the running totals are based on, probably the fields {table.depositamt} and {table.loanamt}, along with available ID fields, so that we can see how the data is repeating as a result of the multiple tables. Then we can figure out how to design the running totals. Please look at my earlier example, and also please explain whether you have an ID associated with each loan and deposit transaction, also as in my example.

-LB

 

[GIScrew]

I apologize for the confusion. The loans and deposit accounts have an object id field, for example, the loan table is records 1 through 2942, and deposit table is records 1 through 37518, making the ids unique but not related. These records are not associated with each other at all, only by geograhic location, ie census tract id. The running totals are based on the following fields:

-Deposit Count
summarize field: {deposits.tct_id}
summary: count
evaluate: every record
Reset: {Group#2:assessmentarea.tct_id}

-Deposit Balance
summarize field: {deposits.balance}
summary: sum
evaluate: every record
Reset: {Group#2:assessmentarea.tct_id}

-Loan Count
summarize field: {loans.tct_id}
summary: count
evaluate: every record
Reset: {Group#2:assessmentarea.tct_id}

-Loan Balance
summarize field: {loans.orig_bal}
summary: sum
evaluate: every record
Reset: {Group#2:assessmentarea.tct_id}

//Right info? There are no duplicate loans or deposits. Each deposit account is held by a different individual. The deposits are actual accounts and not transactions. The loans are unique as well, no duplicates. The only duplicate values are the census ids. Long process made short, we analyze how well financial institutions provide financial services to a diverse population as well as spatial diversity. We first place the loans/deposits based on customer address, the census id is then assigned to their account. 3 different loans could be placed in census id 101.01, but they are all different. The 3 loans in id 101.01 could be Joes loan for his new car, Franks loan for his home, Mary's loan for her new business, etc, etc. Same goes for the deposits. Maybe this made a little sense, maybe not or you're hoping I would stop blabing. Its not difficult reporting if I wasn't a fresh graduate but hey I have to learn. Ideas?
Thanks,
GIScrew

 

[lbass]

But the problem is that the report creates duplication because of your table joins. Please lay out these fields across your details section and then report back with a display of your results:

{assessment.areatct_ID} {deposits.tct_ID} {deposits.balance} {loans.tct_ID} {loan.orig_bal}

If you do this, we can then determine how to change your running totals.

-LB

 

[GIScrew]

Here is a rough example of the results:

id DepID ObjID DepBal LoanID ObjID LoanBal
101 101 650 0 101 566 500
101 101 230 0 101 233 600
101 101 402 250 101 566 500
101 101 402 250 101 431 400
101 101 402 250 101 876 700
101 101 402 250 101 421 800
102 102 123 0 102 200 900
102 102 123 0 102 322 100
102 102 340 0 102 459 200
102 102 607 450 102 200 900

//The Loan Balances repeat throughout the report. I also added the object id to the details section to determine a pattern of repeat loans. For example, my raw data has 7 loans for the tct_id 102.03. The 7 different loans repeat continuously until the tract id changes. The next tct_id 108 has 29 loans, and the 29 loans repeat for the deposits until the tct_id changes. The pattern consistency is moderate. The deposits also repeat as well in some areas of the report. I hope these are the results you requested, similar to your example from above. Don't ask why it I couldn't figure it out earlier today(banging head on desk). Ideas on running totals?
-GIScrew

 

[lbass]

Are these literally a copy of a section of your results or did you make these up? They don't behave the way I think they should. Are there other fields that you are not showing?

You already have a group on {assessmentareaID}. Next sort by {deposit.ObjID} and then by {loan.objID}. For the deposit count and balance, change the evaluate sections of the running totals to evaluate on change of field, and enter {deposit.objID}. The loan field is a little trickier, since I can't see the repetition in your example.

Could you try to provide another example after sorting of the fields? I would expect results more like my example below, where entire sets of loan results are repeated for each deposit:

id ObjID DepBal DepCnt ObjID LoanBal LoanBalRT
101 650 0 1 566 500 500
101 650 0 1 233 600 1100
101 650 0 1 431 400 1500
101 650 0 1 876 700 2200

101 230 0 2 566 500 500
101 230 0 2 233 600 1100
101 230 0 2 431 400 1500
101 230 0 2 876 700 2200

101 402 250 3 566 500 500
101 402 250 3 233 600 1100
101 402 250 3 431 400 1500
101 402 250 3 876 700 2200

Note how the loan info repeats with each {deposit.objID}. If your results are similar to this last example, then your
loan running totals should be set to evaluate for each record, reset on change of field {deposit.objID}. See how the running totals (deposit count and loanbal total) behave in my example above. {#DepCnt} is incremented whenever the {deposit.objID} changes and will be reset on change of assessment ID (not shown). {#LoanBal} is incremented for each record, but reset whenever {deposit.objID} changes.

-LB