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

formula help

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

    formula help

    I have a report that gives the [percentage of students who pass a written and singing test. The problem comes when some students who register for the tests don't show up. They should not be counted as failing because they didn't take it. To complicate matters, I have to break them out by the chorus for which they audition. The formula I have is

    total(if(Chorus="Senior High".and.isblank("Written").and.isblank
    ("Sightsing"),0,1),GRP-"District)

    This should give me the number of senior high students who took the written or sight sing tests but it doesn't and I can't get it to work. I can't just give the no shows a 0 because some of the students actually get a 0. I could give the no shows a negative score but I would prefer not to do that.

    Thanks for your help

    Jim

    #2
    RE: formula help

    James, you don't say what's wrong with the existing formula.
    Does it produce no results, or do you get incorrect results?

    If it were me, I'd define a calc field in the report layout to produce a zero or a 1 as each row gets processed in the report. And then I'd total the calc field.

    Zero if the student is a senior, written is blank and singing is blank, otherwise 1.

    Note: There's a logic issue here. Using your existing formula you'd get a 1 if: student is not a senior OR written is not blank OR singing is NOT blank. Depending on your data and your goal this may be fine, or it may be contributing to the errors you're seeing, since students who take only one test get a 1 and so do students who take both tests, and so do non-seniors.

    -- t

    Comment


      #3
      RE: formula help

      Tom, This formula works to count senior high students who pass both parts of the test. The other kids have different minimums so I have to have 3 formulas.
      total(if(Chorus="Senior High".and.Written"34.and.Sightsing"9,1,0),GRP-"District)
      All three conditions (Senior high, blank and blank values for written and sightsing)have to be met.
      If I enter scores for Senior HIgh only, the formula works correctly. If I enter scores for kids in the other groups, I get incorrect numbers.
      For example I might have four kids who took the senior high test and it correctly reports 4 when ther are no scores for the other kids. If I put in 3 scores for Middle School kids also, it might report 11. If it reported a number that I could trace --like the number of test scores I might be able to figure out what is going on but the number appears to be random -- although I know it's not. The computer does what I tell it -- I just don't don't know what I told it.
      The problem formula also has three conditions and I would use someting like str(written)"0 except that the blank (or null field)is treated as a 0 and some students do get zero scores.
      The null_value function seems to work only in importing operations so I can't use that.
      Thanks for looking.

      Jim

      Comment


        #4
        RE: formula help

        Jim,

        I see you've switched the 0 and the 1 in your formula, as compared to that in your original post. Maybe the best thing is to post a working model here, with instructions for us to follow to see the formula in action.

        -- tom

        Comment


          #5
          RE: formula help

          Here are the table and forms. The problem formula is on the allstatesummary form. If you look at the table, there are five students from astronaut hs who took the test out of 107 senior high students. Three passed and two failed. Those numbers are correct but the problem formula shows 160, which is the total in the file.
          I first built this in version 5. In version 6, I get an error message when I open the calculated fields screen -- it keeps pointing to incorrect formulas but they work correctly and I haven't had time to find the problems. I have to edit through the properties of the formula itself.


          Jim

          Comment


            #6
            RE: formula help

            as usual i forgot the file

            Comment


              #7
              RE: formula help

              Can you be specific, using actual field names? Which specific calculation is errant? What should it show? Which test are you talking about? Does the test actually consist of both singing and writing? Where are these results posted (i.e. which fields for singing? which for writing? which for test taken?)

              -- tom

              Comment


                #8
                RE: formula help

                what denotes passing? what denotes failing? what denotes noshow?

                -- tom

                Comment


                  #9
                  RE: formula help

                  Jim,

                  Using Vers 6 there's something pretty badly mixed up in the calc expressions in your report layout.

                  I created a new layout to show you the approach outlined earlier in this thread. It's a report called "TomsSimpleExample" which shows you the number of records where all the following are true:

                  Chorus = "Treble 7-8"
                  Written " 30
                  SightSing " 7

                  The technique involves creating a simple expression that gets evaluated as each record is processed. The expression returns a 1 if the conditions are met, otherwise it returns a zero. Then, importantly, the simple calc field gets totalled for the group, in the group footer. Very easy. Should be widely applicable througout a summary report such as you are working on.

                  -- tom

                  Comment


                    #10
                    RE: formula help

                    Jim,

                    I've extended the report to include average scores of Treble 7-8 students who pass both tests, by way of example for you.

                    -- tom

                    Comment


                      #11
                      RE: formula help

                      The problem with the example I sent to you by private e-mail is that I didn't include the problem formula. Apparently I didn't save it correctly before I attached it to your e-mail.

                      The correct version is posted above. FYI Passing for the written test for all students is 35 (out of 50) and 10 for the senior high in sight reading and 8 for the other two groups.

                      On the allstatesummary form, I have the offending formula on the far right in big bold numbers (160) and a pointer to it just below with a brief explaination of the issue.
                      I am aware of the fact that alpha dislikes my formulas when I close the calculated fields screen. As I said earlier, when I creatd this application, I was working in version 5 and had no problems and they continue to work under version 6.

                      I had the same issue come up in another applcation and the formulas continue to work. I think the problem was spaces in the formula names, but in this screen I don't see any such problem.


                      Thanks for looking,

                      Jim

                      Comment


                        #12
                        RE: formula help

                        Ok. That helped a lot. I get the desired 102 if I correct a typo, and then count non-test takers, like this:

                        "pre"
                        total(if(Chorus="Senior HIgh".and.isblank("Written").and.isblank("Sightsing"),1,0))
                        "/pre"

                        Comment


                          #13
                          RE: formula help

                          Oops. Permit me to enable HTML

                          Ok. That helped a lot. I get the desired 102 if I correct a typo, and then count non-test takers, like this:

                          Code:
                          total(if(Chorus="Senior HIgh".and.isblank("Written").and.isblank("Sightsing"),1,0))

                          Comment


                            #14
                            RE: formula help

                            I hope the problem is as simple as a misspelling of "Senior".

                            I get the 102 total with the expression

                            total(if(Chorus="Senior HIgh".and.isblank("Written").and.isblank("Sightsing"),1,0))

                            not "Seniot HIgh" and with the ,1,0 usage.
                            There can be only one.

                            Comment


                              #15
                              RE: formula help

                              Sorry, should have re-checked the thread for later answers before I posted. Tom has the answer.
                              There can be only one.

                              Comment

                              Working...
                              X