Average of previous record 1

Place the following formula in your quarter group section:

whileprintingrecords;
numbervar curr;
numbervar prev := curr;
numbervar cnt;
numbervar sumave;
numbervar ave;
curr := sum({table.value},{table.date}, "Quarterly");
if groupnumber <> 1 then (
cnt := cnt + 1;
ave := (curr+prev)/2
);
sumave := sumave + ave;
ave

Then in the report footer, use this formula:

whileprintingrecords;
numbervar sumave;
numbervar cnt;
sumave/cnt

-LB

Thanks LB!!

That was helpful.

I tried to adjust the first formula, but what I was trying was not working.

For each set of values at the quarterly level, which by the way is set to monthly because the report only selects each quarter specified above, I do not want the average taking for the lowest quarter. In this example that means I do not want the average performed for 12/2009.

Also, the quarters are within a property type group, which is within an investor group. Thus, not only do I need a grand total as you demonstrated (thank you), but I need subtotals at the propety type and investor groups as well. I should have mentioned that. My apologies.

Thanks

Well, what would the averages be at the investor and property type levels? Would they be averages of the two quarter averages (continued across groups) or averages of the group averages (which are not the same)?

-LB

Sorry LB, folks here have me working on far too many things at one time I can barely think straight.

After the averages that you showed in the first formula, at the date level, the average of those has to be done at the propety level. That's what I poorly demonstrated in my initial post.

At the investor level and grand total level the results at the property level must be summed so that the investor level is the sum of the property level averages. So what you showed in your first formula is perfect at the date level except all dates, but the first date should have an average. What is happening with what you provided is that for 12/2009 it is averaging itself.

For now, I have done all these steps in Excel after exporting the report with the other core info that I had. I know that this will be a request next year so I was trying to see if I could skip the Excel step.

Thanks a bunch

Change the formula for the dategroup to the following:

//{@accum}:
whileprintingrecords;
numbervar curr;
numbervar prev := curr;
numbervar cnt;
numbervar sumave;
numbervar ave;
curr := sum({Orders.Customer ID},{Orders.Order Date}, "Quarterly");
if groupnumber <> 1 and
{table.propertytype} = previous({table.propertytype}) and
{table.investor} = previous({table.investor}) then
(
cnt := cnt + 1;
ave := (curr+prev)/2
) else
(
cnt := 0;
ave := 0
);
sumave := sumave + ave;
ave;

Then you have to add two reset formulas like this:

//{@reset#2grp} to be placed in the property type group header:
whileprintingrecords;
numbervar curr := 0;
numbervar prev := 0;
numbervar cnt := 0;
numbervar sumave := 0;
numbervar grp2ave := 0;

//{@reset#1grp} to be placed in the investor group header:
whileprintingrecords;
numbervar grp1sum := 0;
numbervar cnt2 := 0;

Then add the following three formulas to display the results (and accumulate for higher levels):

//{@displgrp2ave} for the property group footer:
whileprintingrecords;
numbervar sumave;
numbervar cnt;
numbervar cnt2;
numbervar grp2ave;
numbervar grp1sum;
if cnt <> 0 then
grp2ave := sumave/cnt;
if grp2ave <> 0 then
cnt2 := cnt2 + 1;
grp1sum := grp1sum + grp2ave;
grp2ave;

//{@displgrp1ave} for the investor group footer:
whileprintingrecords;
numbervar grp1sum;
numbervar cnt2;
numbervar grandsum;
numbervar cntall;
numbervar grp1ave;
if cnt2 <> 0 then
grp1ave := grp1sum/cnt2;
grandsum := grandsum + grp1ave;
if grp1ave <> 0 then
cntall := cntall + 1;
grp1ave;

//{@grandave} for the report footer:
whileprintingrecords;
numbervar grandsum;
numbervar cntall;
if cntall <> 0 then
grandsum/cntall

-LB

Thanks LB, I will give it a try

LB - I done the above. Currently, the date group averages are still being done for 12/2009, therefore, the average at the property type level is including that value. I have not verified the sums at the investor and grand total level yet.

My first course of action will be to double check I have done the formulas correctly.

Thanks for you help. This is awesome stuff!!

I got the date group and thus the property type averages working.

I added two formulas to the report using thread767-1109148 called "Display previous group's summarized field" and I added a line to {@accum}.

and Minimum(date,property type) <> @previous date update <-- from above mentioned thread

I am now in the process of reviewing your other formulas to verify if I did them correctly because the investor level is showing as an average, I think, and not as a sum of property type averages.

I thought you wanted an average at all levels. If not, just remove the division by cntall.

I don't know why you are trying to incorporate suggestions from other threads. I tested the above formulas and they worked according to my understanding of your needs. You do need to double check, since 12/2009 did not get incorporated when I did it.

-LB

I understand what you're saying, but I checked, rechecked, and rechecked several more times, but 12/2009 was not showing as 0. I wish I knew why not.

As to the averages vs. sums I had mentioned that.

At the investor level and grand total level the results at the property level must be summed so that the investor level is the sum of the property level averages.

Click to expand...

.

I'll look again. Maybe I am missing something small for the date group.

Thanks again. What you provided has been very helpful.

Blah - the workaround I did for the date level is not going to work in instances where there's only one date for that group so I have to figure out why the specific code you gave is not working for me.

Perhaps a break from this is in order.

By the way making the minor change you mentioned for the grand total sum worked. At the investor level I replaced the last grp1ave with grp1sum and that did the trick.

This is weird. Using LB's date group formula, {@accum} it is working for some, but not all date groups.

The date group is the last group of 6 groups.

I have 5 investors, within each investor there can be up to 5 property types and up to 5 quarters. Using one investor as an example, the first property type average works as desired, so does the last. The other 3 in between, however, do not. Looking at the next investor, none of the first dates work as desired. It is very odd.

Well, this is the first time you've mentioned your other groups. They have to be built into the formula by adding to this section of {@accum}:

if groupnumber <> 1 and
{table.propertytype} = previous({table.propertytype}) and
{table.investor} = previous({table.investor}) and
<add other group fields here and compare to previous value>
then //etc.

-LB