# Thread: Count Instances Between Dates

1. ## 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, ?

3. ## Re: Count Instances Between Dates

Originally Posted by Stan Mathews
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. ## 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.

5. ## Re: Count Instances Between Dates

Originally Posted by Stan Mathews
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. ## 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.

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

8. ## Re: Count Instances Between Dates

Originally Posted by Ton Spies
..... 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. ## 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. ## Re: Count Instances Between Dates

Originally Posted by G Gabriel
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)

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.

12. ## Re: Count Instances Between Dates

Originally Posted by G Gabriel
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. ## 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).

14. ## Re: Count Instances Between Dates

Originally Posted by Stan Mathews
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

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

#### Posting Permissions

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