8 Replies Latest reply on Jun 13, 2017 8:24 AM by beverly

# Calculate records created in Q1

Hi.

I cant find the best way to calculate related records created in Q1, Q2, Q3 and Q4. I want to do this with a autoenter calculating if thats possible with about 2000 records. The related records has a date field that I could count from

• ###### 1. Re: Calculate records created in Q1

"best way" leaves alot to the imagination.. What are you planning on doing with the calculation?

If your building a dashboard type of view you might want to use ExecuteSQL()...

If your looking to build some kind of report in list view then calculations with sub summaries might be the "best way".

Its also important to consider fiscal quarter vs calendar quarter.

• ###### 2. Re: Calculate records created in Q1

autoenter Ceiling(Month(dateField) / 3)

• ###### 3. Re: Calculate records created in Q1

im building a dashboard with overall statuses

• ###### 4. Re: Calculate records created in Q1

thanks, but I have 4 fields, one for each quarter, how would it be for q1 then (January 1-March 31) ?

• ###### 5. Re: Calculate records created in Q1

Why 4 fields ? For 1 date there's only one possible quartile.

set a global gQ to 1, 2,3 or 4,  get sum(rel::relatedRecords) where gQ = Quartile is a predicate in rel

• ###### 6. Re: Calculate records created in Q1

Assuming that you have a calculate field creation_quarter << insert siplus calculation here as good example.

ExecuteSQL ( "SELECT creation_quarter, Count( creation_quarter) FROM the_table GROUP BY creation_quarter";"";"" )

• ###### 7. Re: Calculate records created in Q1

You can use the conditional relationships to get the records for a date range.

set a global (e.g. g1) to the start date, and another global (e.g. g2) to the end date and use

g1 <= date

g2 >= date

in a relationship.

Use the relationship to get your summary values, etc.

• ###### 8. Re: Calculate records created in Q1

There is just one function that deals with "fiscal" (as not all start a "year" - quarterly or otherwise- on 01 JAN.

Because you can specify the 'start date' it may be of benefit. I does return the "week", and you may be able to leverage that value in your "quarters".

Beverly