1. ## Summary and Average

1-Create a simple table with 5 fields:
Field1: Country
field2: State
field3: City
field4: quantity
field5: price

2-Summarize that table on the first 3 fields to get the sum of field4 and average of field5

The sum of field4 will return the correct amount, the average of field5 will not.

Test your results against Excel or just do it manually. The average is incorrect.

I don't think it's the summarize operation per se, but it seems that it has to do with calculating the average. Evidently alpha divideds the total price by the number of records not by the sum of field4 and hence the error.

My tests were done in v8 and I wish someone will check in v8 as well as v9.

Incidentally, same error appears if you do a report with averages.

2. ## Re: Summary and Average

I looked at the figures and are exactly what I would want them to be.

If I wanted to have an average of a field that has been summarized for a group of whatever, I would want the average to be based only upon that group....which it is. So what Alpha is doing is what I would want/expect.

I guess I just do not understand what you expected the outcome to be....

EDIT---I think I see where you are coming from. But I still stand by the above. You are thinking that the average should be based upon the quantity of each item and not the number of records. That is not what I would expect from a summary operation if this is the case.

3. ## Re: Summary and Average

G.
Tried it on v9 using report form and summary and average seems to come out correctly...
I had to create a calc of quanity*price and then took average per grouped city.

4. ## Re: Summary and Average

Originally Posted by MikeC
EDIT---I think I see where you are coming from. But I still stand by the above. You are thinking that the average should be based upon the quantity of each item and not the number of records. That is not what I would expect from a summary operation if this is the case.
Not sure how could this be an Average? This is terribly wrong unless I am missing something major here!

When a salesman in Bostom MA sold at one time 10 widgets @ \$10 a peice (one record) and in another instance sold 1 @ \$20 a peice (another record). The question is: what was the average sale price per peice by this salesman in Boston MA?
The correct answer is: \$10.90 (\$120/11) NOT \$15.00 (30/2).
This is not an average not by any math.

And if alpha base these calculation on the number of records (which is a crazy concept) then how come when it added up the total for quantity, it added it up correctly?
Couldn't possibly use one concept for one calculation and another for the other.

Originally Posted by trackmanpete
G.
Tried it on v9 using report form and summary and average seems to come out correctly...
I had to create a calc of quanity*price and then took average per grouped city.
If you mean you added a calc field to the table, that's a bit different. I thought of doing that and by making a quick calculation in my head, I figured it will still produce the wrong answer as long as alpha is dividing the total by the number of records.

5. ## Re: Summary and Average

I don't know what to tell you G....to me it is obvious that Alpha made a choice when doing the summary operation to use the resultant cost per record in determining the average. You want it to apparently do two summarize operations at the same time. If records have been summarized to produce a cost for that record, I think this negates the possibility of then going back and figuring out the average as you feel it should be. The summary average is just that...an average of the summary.

Your point is valid and perhaps it could be done but as an option to how they currently have it is how I would approach it.

6. ## Re: Summary and Average

I did mention that the first time this caught my eyes was when I used the avg on a report and the numbers didn't look right to me. That was several years ago. I since stopped using avg and did my own calculation. Recently I revisited the issue.

To further show that this is wrong any way you look at it, I created a report based on this same table and used the avg. Got the same wrong results. Looked to see what alpha uses to get the avg: it uses average().

The help file says:
AVERAGE() returns the Average_Value of the Expression evaluated for a group of records.

The Expression is evaluated for all the records in the sub-group that are related to the group’s current record. The resulting values are then added together and the average value is returned.

For example, to summarize the line-items in an invoice set, the Group parameter might be GRP – >INVOICE and the Sub_Group parameter GRP – >INV_ITEM. This means the Summarization operation includes only those child records in INV_ITEM related to the current parent record in the INVOICE table. To summarize all the records in a table or set, use the name GRAND as The Group name. The group GRP – >GRAND and sub-group GRP – >INV_ITEM produce a summary result based on all the invoices.

Hmmm?

7. ## Re: Summary and Average

G,
In your example, if you used the Average of Price/Quantity, I believe you obtain what you expect.

If nothing else, this thread should bring to light the point that one should not assume how something is done when there is more than one way to do something!

8. ## Re: Summary and Average

OK..
On reflection..(deep reflection) on this issue, I determined it's a bug. Not a bug in the summarize operation, not a bug in average(), but a bug in the documentation.

Here is why:

For alpha to obtain the proper avg, it has to know what is the denomiator. Unfortunately, the function does not provide such parameter. The function assumes that you are averaging items in which there is ONE OF EACH. That's a lousy assumption, but it won't be lousy if it let you know that ahead of time. I came to that conclusion based on the example offered in the help file. The help file should document that. It does not.

To clarify further, in my example, I want alpha to divide the total price by the total quantity in order to get the proper avg. But how would alpha to know that's what I want? It wouldn't not. It uses a "Don't ask don't tell" policy. So, the function is LIMITED TO RECORDS THAT HAVE ONE ITEM PER RECORD. This limitation is not documentd in (though should be gleaned from) the documents. I only saw that when investigating what alpha uses to get such average. The limitation is that, the function does not have a denominator parameter and uses the number of records as the denominator.

So here is my suggestion to anyone following this: if you are using an average whether in a summary operation or a report for items that might have more than one per record: you are getting the wrong answer (because you are using the wrong function). This could be very detrimental. You have to create your own UDF and include a parameter for denominator.

That's where I stand unless I hear more convincing argument

9. ## Re: Summary and Average

If you mean you added a calc field to the table, that's a bit different. I thought of doing that and by making a quick calculation in my head, I figured it will still produce the wrong answer as long as alpha is dividing the total by the number of records.
No, it divides by the total of the quantities provided, I think, which is what it should do, that is, total price per city/total quantity per city.
In the report, this is what Alpha generated
Code:
`avg_calc_totalprice_for_City = average(calc->totalprice,grp->City,grp->detail)`

10. ## Re: Summary and Average

Pete:
Is that v9 or v8?
And what is the answer you get for Boston, MA?

11. ## Re: Summary and Average

Interesting topic....

At first I assumed the same issue that Gabe saw, but upon further review, this is an issue of Alpha doing what it is told to do rather than what we want it to do..

Looking at the summarize operation, there is nothing telling Alpha that there is a relationship between field4 (qty) and field5 (price). So how could the average be calculated based on both when the relationship is only defined in our assumption?

Thinking more about it, there are a multiple of ways to accomplish the desired results.

1. 2 passes - run the summarize operations, get the totals, run an update operation and recalc the average..
2. define the relationship between qty and price and pretotal using a dbsum() in the summary operation.
3. Do the work in a report where the calculated fields total up each of the qty and price and the footing averages the total_price/total_qty

The attached sample shows all of the above...

12. ## Re: Summary and Average

Al:
I haven't opened your zip file yet, but I am glad somebody could see what I am talking about. I was only able to decipher this after some deep thoughts with Jack Handy that dawned on me when writing this thread and looking into what alpha uses to obtain the average.

As I indicated earlier, alpha has no way of knowing what denominator you want to use. Problem is, it does not tell you that and if you assume it can read your mind, well, you can't blame alpha for that.

Alpha sometimes does some "smart" thinking on your behalf. I must have assumed if I asked for the total of one field and the average of the other that alpha will "get it". But that's a leap of faith.

I am sure your examples work. I have elected long time ago to do this with a script and it works just fine. I intend to put it in a UDF.

13. ## Re: Summary and Average

Originally Posted by G Gabriel
Pete:
Is that v9 or v8?
And what is the answer you get for Boston, MA?

v9 and from reading Al's response, he seems to find the same...

For Boston I get \$84.50 compared to your \$48.50.

14. ## Re: Summary and Average

Pete:
Now I have just entered the deep caverns of confusion!

Either:
1-Alpha figured out that my expectations were not unreasonable after all and was not a leap of faith (as I said, sometmes alpha does intutive things), figured out the way things were done in v8 were not acceptable and fixed it in v9, or
2-v9 uses a different function than average(). Please check. or

Don't know what to make of this and Jack handy wouldn't be of any more help here as I have no clue how v9 does it.

15. ## Re: Summary and Average

I am not versed enough in fundamentals of Alpha to give you a specific or accurate answer, I just followed your directions and tried to see if I came up with something different using basic Alpha technology (ie. default quick report with summary reports). Enclosed it the sample I was using....

16. ## Re: Summary and Average

Originally Posted by trackmanpete
I came up with something different using basic Alpha technology (ie. default quick report with summary reports). Enclosed it the sample I was using....
Pete

Your \$84.50 is the average of the \$80 for line 1 and the \$89 for line 2.

It's not weighted to 10 items on line 1 and 1 item on line 2. So it is similar to the issue that was found initially...

If you changed your formula to divide the total_price by the total_qty you would get a meaningful number...

17. ## Re: Summary and Average

I must be missing something or reading incorrectly.
Are you and G saying you want \$169/11?

What I did was 10 x \$8=\$80 + 1 x \$89 totals \$169/2 sales records.

The first approach would work using the Alpha Ave formula as I posted, if you created 10 separate records of \$8 each and then added that along with the \$89. This comes to \$15.36. So, then the solution may be to create a calculation incorporating the third approach above, if that is the way you want to go.

18. ## Re: Summary and Average

Originally Posted by trackmanpete
I must be missing something or reading incorrectly.
Are you and G saying you want \$169/11?
Yes

19. ## Re: Summary and Average

What I did was 10 x \$8=\$80 + 1 x \$89 totals \$169/2 sales records.
No, that is not the correct avg.
The correct avg would be \$169/11.
So, I would assume, there is no difference between v8 & v9.

20. ## Re: Summary and Average

Originally Posted by G Gabriel
So, I would assume, there is no difference between v8 & v9.
Everything I did was in v9 and looks the same as previous versions..

21. ## Re: Summary and Average

I just figured out a simple way to fix this and this will be the basis for a UDF:
1-Since alpha will divide the total by the number of the records, and
2-Since we know what the total is,

then

To correct for true average becomes a simple formula:
The avg given by alpha=total_price/# of records.
So, if you multiply that number by the number of records then divide it by the quantity total, you will get the true average.
All you have to do is get the number of records in each sub_groub, like Boston, MA and the formula would be as simple as:
True_Avg=Avg*# of records/Quantity_total for sub_group.

Hmmm!
Why didn't I think of that earlier?

22. ## Re: Summary and Average

Originally Posted by G Gabriel
True_Avg=Avg*# of records/Quantity_total for sub_group.

Hmmm!
Why didn't I think of that earlier?
That's a longer way around to the options that I showed in my examples.

And with each calculation with a computer you introduce time increase (slight) and potential round off error, so do it as straight forward as possible...

23. ## Re: Summary and Average

So I played with it some more in the reports on v9 and I think I was able to also get what each of you were after (just a Johnny come lately as a learning experience)

24. ## Re: Summary and Average

Pete:
I haven't opened your zip file yet.
Obviously, the ways to get an average are numerous as sown in this thread (lets not forget tablesum()/tablecount() as well). The issue in this thread revolves primarily around getting the average in a summary operation. And as has been clarified, one must be aware of the limitation of this operation and the need to use some other means if you wish to get the weighted average.
The script I use is rather very simple and efficient:
It sorts the table according to the desired grouping
It runs through the records once, adding and lastly averaging values for simillar records.
Thanks to all.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•