References
      Back to home
      1. Help Center
      2. References

      Formula Recipes

      A list of formula samples to solve common problems.

      This document contains often-used formula recipes that can help with building reports in Wink Reports. It is recommended to visit the report designer formula reference page to gain an understanding of the functions available.

      Durations / Intervals

      Name Example Notes
      Round a Duration to the nearest whole number 
      INTERVALVALUE(FLOOR(EPOCH([Avg Duration])), "seconds")
      		 After averaging, duration values can end in a decimal value. This can be fixed by using a rounding function.

      However, it first needs to be casted into a number value before it is rounded down.

      This formula rounds down by doing the following:

      1. Take the average duration

      2. Convert it into seconds.

      3. Round down.

      4. Convert back to interval/duration.

      Filter Out Similar Rows

      Sometimes aggregation isn't quite the right solution for trimming out duplicate rows.

      You can instead use the ROWNUMBER() function and a post-aggregation filter to pick which rows you want.

      For example, to only get one row for each [First Name] sorting by [Last Name], add a column "First Name Index" with formula:

       ROWNUMBER([First Name], [+Last Name])

      and a post-aggregation filter:

       [First Name Index] = 1

      Show records older than 4 weeks ago

      While using a custom date range is the best way to show records within a certain time frame, you may wish to use a data source filter as well.

      The following formula will only show records that are older than 4 weeks ago:

      [Date Created] < (NOW() - INTERVALVALUE(4, "weeks"))

      Extract Parts from Text

      Use the regex formulas below with SUBSTRING and REGEXP_REPLACE. For example:

      SUBSTRING([Input Column], "^([0-9\.]+)")

      REGEXP_REPLACE([Input Column], "^([0-9\.]+)", "*** redacted ***")

      We recommend experimenting with the regex formula using a tool like regex101.

      Input Result Regex Formula
      1.165432 - Job Description 1.165432 ^([0-9\.]+)
      1.165432 - Job Description Job Description ^[^-]+ - (.*)

       

      Find the difference between two 12-hour time strings

      Given two Excel-style columns; {End Time} and {Start Time} with a 12-hour string such as "3:oo PM", find the difference between them.

      DATETOTIME(TODAY() + ((INTERVALVALUE(
      (NUMBERVALUE(SPLIT({End Time}, ":", 1)) + IF(SPLIT({End Time}, " ", 2)="PM", 12, 0)) * 60
      + (NUMBERVALUE(SPLIT({End Time}, ":", 2)))
      , "minutes")
      -
      INTERVALVALUE(
      (NUMBERVALUE(SPLIT({Start Time}, ":", 1)) + IF(SPLIT({Start Time}, " ", 2)="PM", 12, 0)) * 60
      + (NUMBERVALUE(SPLIT({Start Time}, ":", 2)))
      , "minutes"))))

      Start time End time Result
      7:00 AM 5:15 PM 10:15:00

       

      Find the difference in months between two dates

      Given two dates, [Start] and [End], return the number of months difference as a whole number.

      (NUMBERVALUE(YEARVALUE([End])) - NUMBERVALUE(YEARVALUE([Start]))) * 12 + (NUMBERVALUE(MONTHVALUE([End]) - NUMBERVALUE(MONTHVALUE([Start])))

      Start End time Result
      26/09/2023 17/07/2024 10.0