Skip to main content
Conga Support

BI Toolbox Contract Flow Stage Duration Best Practices

The BI Toolbox provides Formulas functions to assist with calculating data. The resulting calculation of formulas is then accessible in Reports and Dashboard.

Formulas for Contracts Flow Stage Data 

The following formula allows you to access Contract Flow Stage duration data.

Main Query

This formula returns the sum total number of seconds between when a stage started, when it ended, and if it hasn't ended then it will return information from when it started to the current date. It transforms the query to the total number seconds between an action, and ignores time zone and daylight savings time. There are additional queries available to change seconds into days, hours, and minutes.

 These formulas are only for analysis grids.

case when [stageflowactivity.Stage Flow Activity Completed On] IS null then EXTRACT(EPOCH FROM Now()) - EXTRACT(EPOCH FROM [stageflowactivity.Stage Flow Activity Started On]) else EXTRACT(EPOCH FROM to_timestamp([stageflowactivity.Stage Flow Activity Completed On], 'MM/dd/yyyy HH24:MI:ss')::timestamp without time zone) - EXTRACT(EPOCH FROM [stageflowactivity.Stage Flow Activity Started On]) end

The variables in brackets, such as such as [stageflowactivity.Stage Flow Activity Completed On], are referrering to a field that must be in the data returned in an analysis grid. You can change the field types between Flow Step Data, Flow Stage Date, and Overall Flow Data as shown in the following examples:

Overall Flow

case when[overallflowactivity.Overall Flow Activity Completed On] IS null then EXTRACT(EPOCH FROM Now()) - EXTRACT(EPOCH FROM [overallflowactivity.Overall Flow Activity Started On]) else EXTRACT(EPOCH FROM to_timestamp([overallflowactivity.Overall Flow Activity Completed On], 'MM/dd/yyyy HH24:MI:ss')::timestamp without time zone) - EXTRACT(EPOCH FROM [overallflowactivity.Overall Flow Activity Started On]) end


Flow Stage

case when [stageflowactivity.Stage Flow Activity Completed On] IS null then EXTRACT(EPOCH FROM Now()) - EXTRACT(EPOCH FROM [stageflowactivity.Stage Flow Activity Started On]) else EXTRACT(EPOCH FROM to_timestamp([stageflowactivity.Stage Flow Activity Completed On], 'MM/dd/yyyy HH24:MI:ss')::timestamp without time zone) - EXTRACT(EPOCH FROM [stageflowactivity.Stage Flow Activity Started On]) end


Flow Step 

case when [stepflowactivity.Step Flow Activity Completed On] IS null then EXTRACT(EPOCH FROM Now()) - EXTRACT(EPOCH FROM [stepflowactivity.Step Flow Activity Started On]) else EXTRACT(EPOCH FROM to_timestamp([stepflowactivity.Step Flow Activity Completed On], 'MM/dd/yyyy HH24:MI:ss')::timestamp without time zone) - EXTRACT(EPOCH FROM [stepflowactivity.Step Flow Activity Started On]) end


Additional Queries

You can use these queries to transform the results of the main query into days, hours, or minutes.

 [Seconds Duration] is the name of the main query referenced above. These formulas refer to a total number of units, 
so if you have 1 day for Number of Days, you'll have 24 hours for Number of Hours. 

Days Duration

floor([Seconds Duration] / (24 * 3600))

Number of Hours

floor([Seconds Duration] / 3600)

Number of Minutes

floor([Seconds Duration] / 60)