Averages of Running Total Fields 1

[npp83]

I have performed a count on a column whose type is VarChar. The "string" this column is capturing is a GUID, which is alphanumeric, and can't be cast to an integer safely in order to perform an average on it. I want an average on the count, but the option isn't available for me to do so. Is it possible to perform an average on the running total either using a running total orr a formula field?

In summary: Through a variety of sorting, I have isolated the GUIDs I want to appear. These Guids show up as repeats of themselves based on how I have sorted and isolated them (in this case by Loan Officer since the GUID represents a Loan). I simply wanted to count how many times the GUID shows up, count it, and now show an average of that count.

To show you a better example:

Bob has a single loan, but because it hit the resubmission state 4 times, it shows up 4 times. I made a count of the GUID based on this. Now, lets say Bob has another loan and it shows up only 2 times. I want to take the count of each, add them together and divde by the number of loans. How is this done since Crystal wont let me do an average on a Running Total?

Bob White (Loan Officer)
First Loan:
{1a130376-d61f-4ea0-958c-c7eab52eb98a}
{1a130376-d61f-4ea0-958c-c7eab52eb98a}
{1a130376-d61f-4ea0-958c-c7eab52eb98a}
{1a130376-d61f-4ea0-958c-c7eab52eb98a}
--------------------------------------RT Count: 4-----
Second Loan:
{b4a0d42c-dbea-43e9-a2a2-9ff48d417543}
{b4a0d42c-dbea-43e9-a2a2-9ff48d417543}
--------------------------------------RT Count: 2-----

 

[lbass]

You could use variables like this. Place this formula in the loan group footer section and suppress it.

whileprintingrecords;
numbervar sumcnt := sumcnt + {#yourrt};
numbervar cnt := cnt + 1;

Add this formula to the loan officer group header:
whileprintingrecords;
numbervar sumcnt;
numbervar cnt;
if not inrepeatedgroupheader then (
sumcnt := 0;
cnt := 0
);

In the loan group footer, use this formula to display the average:
whileprintingrecords;
numbervar sumcnt;
numbervar cnt;
if cnt > 0 then
sumcnt/cnt

-LB

 

[npp83]

Thanks, LBass, for the reply. I do have a couple of things I want to point out. I went ahead and took down my personal website to throw on a couple of screen shots I took of both the design and the preview (

http://www.nppetre.com)

Note, I have stored the GUID in the details section, and I left it unsupressed so you can see what its doing - otherwise this should be suppressed. After looking at this, does this change anything with your solution?

What's more, since I am still learning CR. Would I put these formulas in their respective headers within Formula Fields?

 

[lbass]

The results for running totals are generally only correct in footer sections, and in this case, the variable formulas would only be correct there. Your report shows three groups, but the formulas should work if you place them as I said.

-LB

 

[npp83]

First of all, thanks for your continual help, LB.

Ive gone ahead and added in those fomula fields and resuppressed the details section with the new addition. But now I am getting some funky math going on, and the formulas check out in-so-far as I can tell. Ive reposted a screenshot of whats going on now on my website (

http://www.nppetre.com).

It is is the second picture. Is my placement wrong then?

 

[lbass]

Do you have any sections suppressed? If so, identify the sections by group number and also provide the suppression formula. Also indicate in what report sections you placed the formulas, as I can't tell from your screenshot. What fields are you grouping on (in order)?

-LB

 

[npp83]

No, only the Details section was suppressed and I provided an S/S of that. But now I've gone ahead and once again updated my site to show a new SS of the design view with reference to where Ive dropped the new fields. There is also another S/S of the Details section unsuppressed, and as you can see its adding a new way of incrementing the single loans. As you may imagine, it really should only look like this:
{95517eb7-8173-487c-b95e-9e2cc45405dc} 1
{95517eb7-8173-487c-b95e-9e2cc45405dc} 1
{95517eb7-8173-487c-b95e-9e2cc45405dc} 1
{95517eb7-8173-487c-b95e-9e2cc45405dc} 1
-------
4

 

[npp83]

Oh and I forgot to add, I am grouping by 1) Branch, 2) Loan 3) Officer, and Loan.Guid (which are the numbers you see representing the loans and which are being counted).

 

[lbass]

I can't see what sections these are in for sure, but I think you have the accumulation formula in the wrong section--it should be in the same section as the running total--in the Loan GUID group footer.

-LB

 

[npp83]

Ive updated my website. The third picture will show you the groupings/the headers on the right.

If the following code is the accumulation formula you are referring to then it is in the Details section with the GUID - not in the footer, as there is none:

whileprintingrecords;
numbervar sumcnt := sumcnt + {#RTotal0};
numbervar cnt := cnt + 1;

And with that being said, it isnt in the same section/footer as the running total. As per the picture, the running total (#RTotal0) is in Group Footer #3.

 

[lbass]

The formula belongs in GF3.

-LB

 

[npp83]

Wow. Thank you. That worked. The only unwanted side effect of inserting the formulas where they are is that they display unwanted information. Ive updated my site to refect what I mean. So, if you look at the second picture, I've circled in red the values I don't want visible. Basically, I need the count and the average to only show. There wouldnt be a way to suppress these values would there be without havnig to do the whole group?

And thanks again for your help.

 

[lbass]

Yes, of course--in fact, I originally instructed you to suppress the accumulation formula, and I assumed you would suppress the reset. Right click on each field->format field->suppress. There are two forms of suppression--field suppression which is what you will be using for these formulas, and section suppression, which is for the whole section.

-LB

 

[npp83]

That works! Thanks again, LB. I look forward to posting here and helping others with their posts in the near future.