Alpha Video Training
Results 1 to 24 of 24

Thread: Summary and Average

  1. #1
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #2
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default 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.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  3. #3
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default 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.
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  4. #4
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Summary and Average

    Quote Originally Posted by MikeC View Post
    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.

    Quote Originally Posted by trackmanpete View Post
    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. #5
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default 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.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  6. #6
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #7
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default 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!
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  8. #8
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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
    Last edited by G Gabriel; 11-28-2008 at 01:38 PM.

  9. #9
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default 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)
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  10. #10
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Summary and Average

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

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default 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...
    Last edited by Lenny Forziati; 12-01-2008 at 12:01 PM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #13
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default Re: Summary and Average

    Quote Originally Posted by G Gabriel View Post
    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.

    Hope this is helpful.
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  14. #14
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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
    3-v9 offers additional parameters.

    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. #15
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default 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....
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  16. #16
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Summary and Average

    Quote Originally Posted by trackmanpete View Post
    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...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  17. #17
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default 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.
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  18. #18
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Summary and Average

    Quote Originally Posted by trackmanpete View Post
    I must be missing something or reading incorrectly.
    Are you and G saying you want $169/11?
    Yes
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  19. #19
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #20
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Summary and Average

    Quote Originally Posted by G Gabriel View Post
    So, I would assume, there is no difference between v8 & v9.
    Everything I did was in v9 and looks the same as previous versions..
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  21. #21
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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. #22
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Summary and Average

    Quote Originally Posted by G Gabriel View Post
    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...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  23. #23
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default 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)
    "Ollie, remember how dumb I used to be? Well, I'm much better now."

    Pete

  24. #24
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default 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.

Similar Threads

  1. Summary and Average
    By G Gabriel in forum Application Server Version 9 - Web/Browser Applications
    Replies: 2
    Last Post: 11-28-2008, 05:39 PM
  2. Using Average() on form
    By trackmanpete in forum Alpha Five Version 8
    Replies: 7
    Last Post: 05-05-2007, 07:01 PM
  3. Computing Average in Rpt Summary
    By Eric Johnson in forum Alpha Five Version 5
    Replies: 2
    Last Post: 06-21-2003, 05:26 PM
  4. Average
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 12
    Last Post: 12-10-2002, 11:56 PM
  5. summary functions i.e. stddev, variance, average
    By videomuger in forum Alpha Five Version 4
    Replies: 5
    Last Post: 01-12-2001, 01:34 AM

Bookmarks

Posting Permissions

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