IDEA Users Working Remotely: We’re here to help! Please contact our IDEA Help Desk to ensure there is minimal disruption to your daily workflow.

X
Icon


Blog Image

Tech Tip: Working with Max/Min Date Fields


Maximums and minimums can give you great insight into what is going on within the data you’re working with, or within operations. If they appear outside of the norm, further research should be done on those transactions. This Tech Tip provides step-by-step instructions to determine maximum or minimum dates or times, and how to find the record for maximum date AND time.

 

How Do I Determine Maximum or Minimum Date or Time?

 

  1. APPEND FIELD: DTODAYS_DATE
    1. Type: Numeric
    2. Size: 0 decimal places
    3. Parameter: @DtoDays(DATE)
    4. Description: Number of days since 1-Jan-1900
  2. ANALYSIS – SUMMARIZATION
    1. Fields to summarize: Your Choice! MONTH for sample
    2. Numeric fields to total: DTODAYS_DATE
    3. Statistics to include: Maximum & Minimum
  3. APPEND FIELD: MIN_DATE
    1. Type: DATE
    2. Parameter: @DaysToD(DTODAYS_DATE_MIN)
    3. Description: Minimum date
  4. APPEND FIELD: MAX_DATE
    1. Type: DATE
    2. Parameter: @DaysToD(DTODAYS_DATE_MAX)
    3. Description: Maximum date

Functions for time are:

@TtoN – Converts a Time field that has values stored in Time format (HH:MM:SS) to a Numeric field with the time value converted into seconds.

@Ntot –  Converts a Numeric field containing time stored as seconds to a Time field type with a Time format (HH:MM:SS).

WAIT A MINUTE! I need to find the record for maximum date AND time.

  1. APPEND FIELD: DATE_TIME
    1. Type: Numeric
    2. Size: 5 decimal places
    3. Parameter: @Val(@Dtoc(DATE,“YYYYMMDD”) + “.” + @Replace(@Str(@Tton( TIME ),5,0),” “,“0”))
  2. ANALYSIS – SUMMARIZATION
    1. Fields to summarize: Your Choice! MONTH for sample
    2. Numeric fields to total: DATE_TIME
    3. Statistics to include: Maximum
  3. ANALYSIS – JOIN
    1. Primary database: SUMMARIZATION
    2. Secondary database: DATE_TIME DATA
    3. Match Key Fields:
      1. MONTH <-> MONTH
      2. DATE_TIME_MAX <-> DATE_TIME
    4. Matches Only


Best Practices , CaseWare IDEA , Tech Tip



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Tech Tip: Fuzzy Duplicates & Fuzzy Match
Apr 21 CaseWare IDEA® Version 10 introduced an Advanced Fuzzy Duplicate task, which identifies multiple similar records for up to three selected character fields. The...
Healthcare
Jan 01 Accounts Receivable, Patient Billing and Managed Care   Calculate average days from discharge to bill and bill to payment by payer or department D...
Common @Functions in IDEA
Jul 12 FUNCTIONS FUNCTIONALITY EXAMPLES @age( ) Returns the number of days between two specified dates. Note: both dates must be a "Date" field typ...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×