10 Replies Latest reply on Jun 16, 2016 10:03 AM by okramis

# Calculate number of records within current month

I am looking to be able to calculate how many records fall within the current month.

the field I need to count is called NextContact.

I was looking at something like

Case

(

NextContact=*/*/2016;1;0

)

Then a summary to count the number found. I can not get the above to work, could someone tell me the formula to get the count I require.

Thank you in anticipation of your help.

MT

• ###### 1. Re: Calculate number of records within current month

You could do a ExecuteSQL like this:

ExecuteSQL ( "

SELECT COUNT(*)

FROM YourTable

WHERE MONTH(NextContact)=? AND YEAR(NextContact)=?

" ; "" ; "" ; Month ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ))

)

Best regards

Otmar

• ###### 2. Re: Calculate number of records within current month

Thank you that worked perfectly.

• ###### 3. Re: Calculate number of records within current month

Hi Okramis,

I thought that had worked however it is giving me a result for each record regardless of wether there is date in the main field "NextContact" or wether it is empty.

Can you offer any help on your original idea.

Thanks

• ###### 4. Re: Calculate number of records within current month

Hi michaelt1000,

Where do you put the calculation?

As this calculation is context independent, you would probably fill a global or \$\$variable with it (best through a script) and place it on a overview layout, or in the header-/footer-/summery-part of your report. It looks like you want to put it as calculated-field in your table holding the records - that would give you the same result on every record.

• ###### 5. Re: Calculate number of records within current month

Hi,

Ideally I was trying to show in the Header,

I am trying to create 3 total fields that work of 3 calculation fields that show

1. Number Of Calls Due Today

2. Number Of Calls overdue all together

3. Number of Calls that are in this current month

I have done the first 2 using Calculation field and Case option.

The 3rd one is causing me the issue, Ideally I was trying to set that where a record has current months date in it was set to 1, then another Summary Field counting the total.

Thanks

• ###### 6. Re: Calculate number of records within current month

I am looking to use the below I think?!?

Case

(

MonthName(Next Contact Date) & " " & Year(Next Contact Date);1

)

And then a summary field to calculate total number of records.

• ###### 7. Re: Calculate number of records within current month

>3. Number of Calls that are in this current month

An alternate method would be to create a related table.

• From Date
• To Date
• Calculate Related Values

Pre-populate the new table with date ranges (i.e. From 5/1/2016;  To 5/31/2016).  Calculation looks back at your original table and counts the related records that fall between the 'from' and 'to' date fields.

• ###### 8. Re: Calculate number of records within current month

Can you post a sample file? Would be easier to explain...

• ###### 9. Re: Calculate number of records within current month

Quick db created.

Table Structure

• Main Table
• z_Next Contact

Field Def (Main Table)

• Next Contact (as Date)

Field Def (z_Next Contact)

• dteFrom (as Date)
• dteTo (as Date)
• c_CountMonth (as Calculation)
= Count ( Main Table::NextContact )

Relationship

• NextContact >= dteFrom
• NextContact <= dteTo
• ###### 10. Re: Calculate number of records within current month

here's a sample (mark.picozzi: took your file as a base). The magic happens in the script "refresh Analysis", where the global field "Analysis" gets filled. The script gets triggered with the layout trigger "on record commit".