Alpha Video Training
Results 1 to 14 of 14

Thread: Count Instances Between Dates

  1. #1
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default 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. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

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

  3. #3
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

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

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

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

  5. #5
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

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

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

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

  7. #7
    Member Ton Spies's Avatar
    Real Name
    Ton Spies
    Join Date
    Apr 2002
    Location
    Leeuwarden, The Netherlands
    Posts
    555

    Default 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

  8. #8
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

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

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

    Default Re: Count Instances Between Dates

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

  10. #10
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

    Quote 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 at 09:30 PM. Reason: additional Info

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

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

  12. #12
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

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

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

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

  14. #14
    Member Chuck Bytes's Avatar
    Real Name
    Charles Luke
    Join Date
    Nov 2009
    Location
    Toronto, Ontario, Canada
    Posts
    138

    Default Re: Count Instances Between Dates

    Quote 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

Similar Threads

  1. Counting instances (dates)
    By xido in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 02-25-2009, 09:38 AM
  2. Licensed user count and network users count
    By Rokrz in forum Alpha Four Versions 7 and 8
    Replies: 0
    Last Post: 05-17-2007, 11:49 AM
  3. Two Instances of Startup Form
    By Shirley in forum Alpha Five Version 6
    Replies: 3
    Last Post: 03-23-2007, 04:55 PM
  4. record count - delete button adjusting count
    By dik_coleman in forum Alpha Five Version 7
    Replies: 3
    Last Post: 12-23-2005, 12:06 PM
  5. Two instances of A4v8?
    By Paul H in forum Alpha Four Versions 7 and 8
    Replies: 2
    Last Post: 10-06-2003, 12:32 PM

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
  •