Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Summary and Average

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    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
    __________________________________________



    Comment


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

      Pete

      Comment


      • #4
        Re: Summary and Average

        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.

        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.

        Comment


        • #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.
          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
          __________________________________________



          Comment


          • #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?

            Comment


            • #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!
              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
              __________________________________________



              Comment


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

                Comment


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

                  Pete

                  Comment


                  • #10
                    Re: Summary and Average

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

                    Comment


                    • #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...
                      Last edited by Lenny Forziati; 12-01-2008, 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.
                      "Make it as simple as possible, but not simpler."
                      Albert Einstein

                      http://www.iadn.com/images/media/iadn_member.png

                      Comment


                      • #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.

                        Comment


                        • #13
                          Re: Summary and Average

                          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

                          Comment


                          • #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
                            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.

                            Comment


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

                              Pete

                              Comment

                              Working...
                              X