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

Count Instances Between Dates

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

  • Count Instances Between Dates

    Could I get a recommendation on what function to use if I wanted to count the number of instances between specified dates.
    Does Alpha have a function that returns a value for week numbers...

    Field is D1 (Date Field)
    5 Instances = (Jan 19) returns week 3
    6 Instances = (Jan 21) returns week 4
    7 Instances = (Jan 29) returns week 5

    Calculate Function
    F1 = Count number of times D1 value is 3
    F2 = Count number of times D1 value is 4
    F3 = Count number of times D1 value is 5

    Result
    F1 = 11
    F2 = 7
    F3 = 0

  • #2
    Re: Count Instances Between Dates

    What have you discovered thus far in investigating available functions in the help file?

    How did you arrive at

    (Jan 19) returns week 3
    (Jan 21) returns week 4

    The first seven days (week) of 2010 (1-7)
    The second seven days (week) of 2010 (8-14)
    The third seven days (week) of 2010 (15-21)
    The fourth seven days (week) of 2010 (22-28)

    Where do you want to see the result? Form, report, ?
    There can be only one.

    Comment


    • #3
      Re: Count Instances Between Dates

      Originally posted by Stan Mathews View Post
      What have you discovered thus far in investigating available functions in the help file?

      How did you arrive at

      (Jan 19) returns week 3
      (Jan 21) returns week 4

      The first seven days (week) of 2010 (1-7)
      The second seven days (week) of 2010 (8-14)
      The third seven days (week) of 2010 (15-21)
      The fourth seven days (week) of 2010 (22-28)

      Where do you want to see the result? Form, report, ?
      Actually I only used examples...I looked at the calendar and counted.
      When I used the Week() function, it returned a strange value for anything last year.
      So if I read your response correctly, 1-7 regardless of how it shows on a calendar is week 1

      I want to see result in report. Basically a MS Project format where dates are titled and cells below show a count of instances under that title

      Jan 18-23 = 5 instances
      Jan 24-30 = 7 instances

      etc.....

      Comment


      • #4
        Re: Count Instances Between Dates

        I was just asking how you determined how to return the week number to know what you wanted. If you use Alpha's week() funtion

        Code:
        for i = 1 to 31
        ? cmonth({12/31/2009}+i)+" "+padl(alltrim(str(i)),2,"0") + " Week "+week({12/31/2009}+i)
        next i
        January 01 Week 1
        January 02 Week 1
        January 03 Week 2
        January 04 Week 2
        January 05 Week 2
        January 06 Week 2
        January 07 Week 2
        January 08 Week 2
        January 09 Week 2
        January 10 Week 3
        January 11 Week 3
        January 12 Week 3
        January 13 Week 3
        January 14 Week 3
        January 15 Week 3
        January 16 Week 3
        January 17 Week 4
        January 18 Week 4
        January 19 Week 4
        January 20 Week 4
        January 21 Week 4
        January 22 Week 4
        January 23 Week 4
        January 24 Week 5
        January 25 Week 5
        January 26 Week 5
        January 27 Week 5
        January 28 Week 5
        January 29 Week 5
        January 30 Week 5
        January 31 Week 6
        you need to understand the number it generates.

        The WEEK() function returns the integer equal to the week number (1-53) for the specified Date_Value. A week includes any number of days in the year. For example, if January 1 is on a Saturday, it is in week 1. January 2 would be in week 2.
        There can be only one.

        Comment


        • #5
          Re: Count Instances Between Dates

          Originally posted by Stan Mathews View Post
          I was just asking how you determined how to return the week number to know what you wanted. If you use Alpha's week() funtion

          Code:
          for i = 1 to 31
          ? cmonth({12/31/2009}+i)+" "+padl(alltrim(str(i)),2,"0") + " Week "+week({12/31/2009}+i)
          next i
          January 01 Week 1
          January 02 Week 1
          January 03 Week 2
          January 04 Week 2
          January 05 Week 2
          January 06 Week 2
          January 07 Week 2
          January 08 Week 2
          January 09 Week 2
          January 10 Week 3
          January 11 Week 3
          January 12 Week 3
          January 13 Week 3
          January 14 Week 3
          January 15 Week 3
          January 16 Week 3
          January 17 Week 4
          January 18 Week 4
          January 19 Week 4
          January 20 Week 4
          January 21 Week 4
          January 22 Week 4
          January 23 Week 4
          January 24 Week 5
          January 25 Week 5
          January 26 Week 5
          January 27 Week 5
          January 28 Week 5
          January 29 Week 5
          January 30 Week 5
          January 31 Week 6
          you need to understand the number it generates.

          Excellent, good info. Now I just have to figure out how to count the number of instances for each resulting week. I'm trying to avoid creating a calculated field for each week interval, but I don't think there is a way around it.

          Sorry if I didn't make that question clear. I think I should have focused my question more on how to create a calculated field that counts the number of results that equal the week number.

          That way when report moves in time, I wouldn't have to populate it with the field that calculates for a block of weeks.

          Comment


          • #6
            Re: Count Instances Between Dates

            I'm trying to avoid creating a calculated field for each week interval, but I don't think there is a way around it.
            This is generally handled by creating the appropriate grouping on the report and placing the desired summary calculation in the group header or footer.
            There can be only one.

            Comment


            • #7
              Re: Count Instances Between Dates

              ..... and if you ever get in trouble with the system of the weeknumber Alpha supports two versions to calculate the weeknumber.


              ?? week(date())
              4
              ?? week_iso(date())
              3

              Ton
              Most things are simple but unfortunately only after the first time

              Comment


              • #8
                Re: Count Instances Between Dates

                Originally posted by Ton Spies View Post
                ..... and if you ever get in trouble with the system of the weeknumber Alpha supports two versions to calculate the weeknumber.


                ?? week(date())
                4
                ?? week_iso(date())
                3

                Ton

                Thanks Ton, Good to know ....
                This will probably confuse the heck out of anyone reading.

                I've worked out my issue but it's very painful.

                1) first determine the week number
                2) then write a command to place a 1 for each instance of each week
                3) then count the instances (1's) for each week - this hurts because the only way i could think of doing it is to create a calculated field for each week.

                Count if week 3, count if week 4, count if week 5 etc...

                that's a lot of calculated fields. Especially if I have to do it for 15 different types of fields to count...So 15 x 49 (weeks left in 2010) = that's 735 calculations. I stopped after 15 weeks, i'll have to do it again in a couple months.

                I was hoping there would be one command that would tell me the count of instances for each week.

                Field 1 - week 1 = 5
                Field 1 - week 2 = 7

                Field 2 - week 1 = 3
                Field 2 - week 2 = 9

                etc....

                Comment


                • #9
                  Re: Count Instances Between Dates

                  I do not understand the question of this thread?
                  What "instances" are you trying to calculate?
                  Instances of what?

                  Comment


                  • #10
                    Re: Count Instances Between Dates

                    Originally posted by G Gabriel View Post
                    I do not understand the question of this thread?
                    What "instances" are you trying to calculate?
                    Instances of what?

                    Well the instance in my case is .not.isblank so if there is a date populated, the value is the instance.

                    Record 1 Field 1 = Jan 20
                    Record 2 Field 1 = Jan 21
                    Record 3 Feild 1 = Jan 21

                    Summary

                    Jan 20 = 1 instance
                    Jan 21 = 2 instances


                    So this way I can see how many records contain a value of a date between any given period of time which is equal to a week number.

                    Lets say Jan 20 and 21 are in week 4. there is a total of 3 instances in week 4


                    I attached a picture portion of my current report results and calculations.
                    That one line titled Planned Start required 4 calculated fields so I can count the number of times a date is filled out for that week.
                    That's 15 weeks of data, each cell contains the following formulas.

                    Now as you can all see, I'm no expert at Alpha and am just using the comman sense stuff. But I'll bet there is an easier way to provide the same results.

                    1) Assign a week number to field, each cell contains this formula to return a week number
                    CALC->Week_Planned_Start
                    week(Planned_Start)

                    2) Count if field is not empty, this will turn the value to a number so it can be used in a calculation.
                    CALC->Planned_Start_1 or 0
                    if(.NOT.isblank("Planned_Start"),1,0)

                    3) Assign a value in the week it happens. This also provides a number to every instance where the value is in week 4.
                    CALC->Plan_Start_Wk4
                    If(calc->Week_Planned_Start=4,1,0)

                    4) Total at cell value for that week. This calculates the number of instances in week 4
                    CALC->total_Plan_start_wk4
                    Total(calc->Plan_Start_Wk4,GRP->GRAND)


                    This additional formula is used to calculate the grand total.

                    5) Grand Total the value of all non blank for that field
                    CALC->Total_Planned_Start
                    Total(calc->Planned_Start_1or0,GRP->GRAND)
                    Last edited by Chuck Bytes; 01-21-2010, 09:30 PM. Reason: additional Info

                    Comment


                    • #11
                      Re: Count Instances Between Dates

                      You could either:
                      1-Insert a group break in the report based on expression and the expression is based on week().
                      The report is filtered to show dates that are not blank.
                      Drag and drop the date field in the group footer and check "count".
                      This will give you a report with a different appearance where each week and its count is on a row. Or:

                      2-If you want the landscape appearance you have:
                      Run a summary or a crosstab operation that creates a table with each week's count, then use that in the report instead of your original table.

                      Comment


                      • #12
                        Re: Count Instances Between Dates

                        Originally posted by G Gabriel View Post
                        You could either:
                        1-Insert a group break in the report based on expression and the expression is based on week().
                        The report is filtered to show dates that are not blank.
                        Drag and drop the date field in the group footer and check "count".
                        This will give you a report with a different appearance where each week and its count is on a row. Or:

                        2-If you want the landscape appearance you have:
                        Run a summary or a crosstab operation that creates a table with each week's count, then use that in the report instead of your original table.

                        Let me try that....It's going to take some learning as I'm not familiar with those concepts. I'll let you know how that works for me.

                        Comment


                        • #13
                          Re: Count Instances Between Dates

                          Did you look at the sample I attached earlier?

                          It has a grouping by week (and year in case the report spans the end of a year). It also demonstrates converting the week number and year number to character to allow concatenation (instead of addition).
                          There can be only one.

                          Comment


                          • #14
                            Re: Count Instances Between Dates

                            Originally posted by Stan Mathews View Post
                            Did you look at the sample I attached earlier?

                            It has a grouping by week (and year in case the report spans the end of a year). It also demonstrates converting the week number and year number to character to allow concatenation (instead of addition).

                            Sorry Stan, I've done my best to evaluate what you sent. Held off replying until I gave myself a chance to break it down. You'll have to forgive my lack of knowledge but I just don't have the experience to understand what you're showing me.

                            From what I'm seeing, you've provided a report that groups the weeks and totals them by group. 7 instances per week. That makes sense, 7 days in a week.

                            I'm just not seeing where or how it returns a count for the number records and Identifies the week or year.

                            In this attached picture I show
                            1. The Records
                            2. The first grouping which counts the number of instances for each week
                            3. The summary which provides the total count for each week
                            Here is what I want the final report to show

                            Should read
                            Count for Week 4 2009 = 1
                            Count for Week 5 2009 = 1
                            Count for Week 5 2010 = 3
                            Count for Week 6 2009 = 2
                            Count for Week 10 2010 = 2
                            Count for Week 19 2010 = 1
                            Count for Week 23 2009 = 1

                            Comment

                            Working...
                            X