Easy Insight Logo
Call 1-(720)-316-8174

Interval Functions

Interval functions enable you to calculate point in time reports. For example:

  • Looking at deal value in the pipeline over time. Instead of seeing just when deal value was created or when deal value was closed, seeing how much was in the pipeline each month.
  • Understanding work load on projects or tasks over time. Instead of seeing just when tasks are created or completed, seeing how many open tasks each assignee each day.
  • Visualizing how many tickets have open at a point in time. Instead of seeing just when tickets are created or resolved, seeing how many open tickets have been in progress each day.

In all of these examples, the data typically has a start date, an end date, an identifier and one or more measures. The goal is to sum that measure for each date and identifier going from start date to end date.

To accomplish this goal, you can use the fill and fillinterval functions. They work in the same way, but fill clears out the identifier and date fields so that they won't get in the way of any calculations or aggregation you may have. Both functions have the same syntax:

fill([Task ID], [Task Start Date], [Task Due Date])

fillinterval([Task ID], [Task Start Date], [Task Due Date])

case a case statement, also known as a switch statement, is used for conditional logic. A case or switch statement allows for selection control in order to allow the value of a variable or expression to change the control of flow of a program's execution via a conditional statement or multiway branch.

case([Field], "x", 1, "y", 2, 3)

would return 1 if Field is x, return 2 if Field is y, and 3 if anything else.

if

if for conditional logic

if([Field1] > [Field2], 1, 0)

will return 1 if the value of Field1 is greater than the value of Field2

if([Field1] >= [Field2], 1, 0)

will return 1 if the value of Field1 is greater than or equal to the value of Field2

if([Field1] == [Field2], 1, 0)

will return 1 if the value of Field1 is equal to the value of Field2

if([Field1] <= [Field2], 1, 0)

will return 1 if the value of Field1 is less than or equal to the value of Field2

if([Field1] < [Field2], 1, 0)

will return 1 if the value of Field1 is less than the value of Field2

if([Field1] != [Field2], 1, 0)

will return 1 if the value of Field1 is not equal to the value of Field2

if(([Field1] > [Field2]) && ([Field3] > [Field4]), 1, 0)

will return 1 if the value of Field1 is greater than the value of Field2 AND the value of Field3 is greater than the value of Field4

will return 1 if the value of Field1 is not equal to the value of Field2

if(([Field1] > [Field2]) || ([Field3] > [Field4]), 1, 0)

will return 1 if the value of Field1 is greater than the value of Field2 OR the value of Field3 is greater than the value of Field4

RESOURCES
Twitter Logo