"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.
autoenter Ceiling(Month(dateField) / 3)
im building a dashboard with overall statuses
thanks, but I have 4 fields, one for each quarter, how would it be for q1 then (January 1-March 31) ?
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
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";"";"" )
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.
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".