Knack Operations
  • TABLE OF CONTENTS
  • Knack Operations Read-Only
  • Knack
    • Platform Overview
    • Functionality Guide
    • Style Guide
    • Single Sign On (SSO)
      • Configuring a new Knack App with certificates
      • Enabling SSO in Knack
      • Configuring new certificates for all Knack Apps - When certs expire!
        • SSL Certificate Management
      • Configuring an existing Knack App with certificates (Rare)
    • Banner Logos
    • Rounding Knack Costs Correctly
    • URL Parameters to populate a form
    • Importing Records
    • Copy an Application
  • Knack Integrations
    • Open Data Portal - Socrata Integration
    • ArcGIS Online Integration
    • CSR configuration prep in Knack
      • Creating CSR Page components
  • Knack Code
    • Code Version Control
    • GitHub Code Workflow
    • CODE: Retrieve builder URL of Email Rules
    • CODE: Mobile Optimization
      • Detail Labels
    • CODE: Custom Login Buttons
    • CODE: Vision Zero in Action
    • CODE: Knack Functionality
      • Big Buttons
      • Save Knack Record ID
      • Redirect Blank Menu Pages
      • Disable Trigger Buttons
      • Disable Modal Background Click/Touch
      • Disable Breadcrumb Navigation Links
      • Remove Back Links
      • Back Link Buttons
      • Return to Previous Page Button
      • Auto Input Validation Form Fields
      • Conditionally Hide/Show Form Fields
      • Return to Top Button
      • Refresh View Button
      • Detect IE
      • Set Map iFrame Source URL
    • CODE: Knack Look & Feel
      • Multipage Step Indicator
      • Modal Close Styling
      • Relabel Attachment Links
      • Navigation Dropdown Menu Buttons
      • In-Form Dropdown Menu Buttons
      • Trigger Buttons
      • Hyperlink a Form Field Label
      • Remove Table Header
      • Modify Table Summary Rows
      • Convert Values to UPPERCASE (Option A)
      • Convert Values to UPPERCASE (Option B)
      • Highlight Details View Label Body & Table Value
      • Report Headers
      • Report Filters Container Size
      • Report Element Colors
      • HighCharts Report Styling
      • Font Awesome Icons
    • CODE: Knack Print Page
      • Print Header with Page Break/Number
      • Print without overlapping views
      • Print using Menu Button
      • Print using Trigger Button
      • Show Character Limit of Input
      • Add fixed Footer on Print
  • AMD | DATA TRACKER
    • AMD | Data Tracker
    • Asset Changes
    • CSR | Flex Note Archiving
    • Cameras
    • Fix Broken CCTV Device Status Page Links
    • Performance Measures
    • Builder Notes
      • Configure New AMD Asset in Data Tracker
      • Data Tracker | Annual Signal and PHB Request Ranking
      • Page Rules (TMC) CSR
      • NOTIFICATION | Construction Status Email
      • NOTIFICATION | One Day Signal Engineer Due Date
      • DETAILS | Preventative Maintenance (PM)
      • DETAILS | Signal Detectors Object Tables, Reports, misc..
      • DETAILS | Signals
      • Signals Work Order | EDIT FORMS
      • Signals Work Order | FORM LOCATIONS
      • Editing Signals Work Order | NEW FORMS
    • Warehouse Inventory - Updating Journal Vouchers (JV) information
    • Cancel Transactions & Replenishments
    • Adjustment Transaction, Unit Cost Adjustments & Re-Stock
    • DTS | Adminstrative Action
  • DTS | Data & Technology Services Portal
    • DTS | Data & Technology Services Portal
    • Dataset inventory
    • Application Inventory
  • Finance & Purchasing
    • Finance & Purchasing Portal
    • Finance & Purchasing
      • Training, Quiz, Account Access Process
      • Statuses
      • List of emails generated
      • Re/Un-assigning Approver of a PR (Purchase Request)
      • Troubleshooting: FAQs
    • Warehouse Inventory
      • Adding a new inventory item
  • HiRe | The Hiring Resource
    • HiRe | The Hiring Resource
  • HR | Human Resources
    • HR | Human Resources Portal
    • How to add a new HR Form
  • PEP | Parking Enterprise Portal
    • PEP | Parking Enterprise Portal
    • Dispatch Resource Tracking Log Archiving
    • Update Parking Citation Report by Fiscal Year (FY)
  • ROW | Right of Way Portal
    • ROW | Right of Way Portal
    • COS JV Update
  • RPP | Residential Parking Permit Portal
    • RPP | Residential Parking Permits
    • Residential Parking Permits
    • RPP Migration
  • SBO | Street & Bridge Operations
    • SBO | Street & Bridge Operations
  • SMD | Signs & Markings Operations
    • SMD | Signs & Markings Operations
    • Table of Contents
    • Workflow
    • Markings | Work Orders
      • Process & Module Functionality
    • Markings | Service Requests
      • Process & Module Functionality
    • Signs | Work Orders
      • Process & Module Functionality
    • Signs | Service Requests
      • Process & Functionality
    • Contractor | Work Orders
      • Process & Module Functionality
    • Sign Fabrication Work Orders
      • Process and Functionality
    • Special Functions/Codes
      • Work Order Attachments
  • SMO | Smart Mobility Operations
    • SMO | Smart Mobility Portal
  • SMS | Shared Mobility Services
    • SMS | Shared Mobility Portal
    • License & Permit Management
    • Sources for Monthly Trip Records
  • STREET BANNER PROGRAM
    • Street Banner Program
    • Street Banner Program
  • TDS | Transportation Development Services
    • TDS | Transportation Development Services Portal
    • Color Palette
    • Role Permissions Guide
    • App Logic
      • Calculating Date Values for Reporting
      • Incrementing Review Cycle Automatically
      • Calculating Review Time & On Time Percentage
      • Calculating Cycle Completion Percentage
      • Calculating an Expiration Date
      • Automatic Due Date with Manual Adjustment
      • Roll up Child Object Conditionals
      • Converting an Entered Percentage to a Percentage Value
      • Create a Connection Link Field to View a Searched Record
      • Extracting Location Coordinates to add a Map Marker Map
    • TDR | Development Reviews
    • TIA | Traffic Impact Analysis
    • TDA | Transportation Development Assessments
  • Traffic Register
    • Traffic Register
    • Table of Contents
    • Workflow
    • Modules
    • Special Functions/Code
    • Permissions
  • TPW Forms
    • TPW Forms
  • VZA | Vision Zero in Action
    • VZA | Vision Zero in Action
    • Batch Copy Process for VZA Assignments
    • Calculate School Closures
    • Login Process for Officers
    • Officer Records
    • Update reports in Power BI
    • Use Tasks for Creating Child Records
    • Use Tasks for Notifications
  • O365 Resources
    • Resources
Powered by GitBook
On this page

Was this helpful?

  1. TDS | Transportation Development Services
  2. App Logic

Calculating Date Values for Reporting

How to calculate date values for reporting

For certain reports there is a need to display, group, or filter data by date values such as Month Name, Month Number, Quarter Number, Fiscal Quarter Number, or Year Number. We utilize several Knack equation functions in order to calculate these date values for each record to fine tune reporting needs for an application due to limited reporting functionality in Knack.

Required Fields

  • Text Formula - MonthName

  • Equation - Month Number

  • Equation - Quarter Number

  • Equation - Fiscal Quarter Number

  • Equation - Year Number

These fields can be built in any object where there is a reporting need.

Equation Type must be set to Numeric for all of these. No other formatting is necessary.

Created Month Name

The getDateMonthOfYearName function pulls the name of the month for our Case Created Date. Since this is text, we use a text formula.

getDateMonthOfYearName({Case Created Date Time})

For our equation logic, we utilize Ternary Operators to calculate and set our desired values based on certain conditions. A simple way to remember is whatever precedes the ? is the IF statement, after the ? is the THEN statement, and after the : is the ELSE statement. Multiple : can be used to create an ELSE IF statement, which we do below for the Fiscal Quarter equation.

Created Month Number

The getDateMonth function pulls the month number for our Case Created Date if not blank but sets this equation to blank if Case Created Date is blank

{Case Created Date Time} != 0 ? getDateMonth({Case Created Date Time}) : null

Created Quarter Number

The getDateQuarter function pulls the quarter number for our Case Created Date if not blank but sets this equation to blank if Case Created Date is blank

{Case Created Date Time} != 0 ? getDateQuarter({Case Created Date Time}) : null

Created Fiscal Quarter Number

We used the Created Quarter Number equation above to do some basic math to determine the corresponding fiscal quarter. If quarter 4, we subtract 3 since this would be fiscal quarter 1. If quarter is blank we set this to blank but otherwise we add 1 to the quarter number.

Quarter Number

Fiscal Quarter Number

1

2

2

3

3

4

4

1

{createdQuarterNumber}== 4 ? {createdQuarterNumber} - 3 : ({createdQuarterNumber} == 0 ? null : {createdQuarterNumber} + 1)

Created Year Number

The getDateYear function pulls the year number for our Case Created Date if not blank but sets this equation to blank if Case Created Date is blank

{Case Created Date Time} != 0 ? getDateYear({Case Created Date Time}) : null

This is especially important since Knack defaults date years to 1970 and this functionally ruins reporting. We make sure the equation is blank if there is no Created Date.

PreviousApp LogicNextIncrementing Review Cycle Automatically

Last updated 3 years ago

Was this helpful?