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?
APPEND FIELD: DTODAYS_DATE
Type: Numeric
Size: 0 decimal places
Parameter: @DtoDays(DATE)
Description: Number of days since 1-Jan-1900
ANALYSIS – SUMMARIZATION
Fields to summarize: Your Choice! MONTH for sample
Numeric fields to total: DTODAYS_DATE
Statistics to include: Maximum & Minimum
APPEND FIELD: MIN_DATE
Type: DATE
Parameter: @DaysToD(DTODAYS_DATE_MIN)
Description: Minimum date
APPEND FIELD: MAX_DATE
Type: DATE
Parameter: @DaysToD(DTODAYS_DATE_MAX)
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.
APPEND FIELD: DATE_TIME
Type: Numeric
Size: 5 decimal places
Parameter: @Val(@Dtoc(DATE,“YYYYMMDD”) + “.” + @Replace(@Str(@Tton( TIME ),5,0),” “,“0”))
ANALYSIS – SUMMARIZATION
Fields to summarize: Your Choice! MONTH for sample
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.
Oct 23
Excel on Steroids
Firm: Wipfli
Product: CaseWare IDEA®
Audit Function: Data Analysis
On Record: Matt Storlie, Manager of Forensic & Litigation ...
May 20
How Data Analysis Tools Empower the Internal Audit Function
Our ominous economic situation has prompted many companies to rethink how they conduct busi...
Jul 20
Where to Look and How to Find Them
Fraud is on the rise. And if you don’t know what fraud looks like, you’re not likely to find it. While most oc...
BROWSER NOT SUPPORTED
This website has been designed for modern browsers. Please update. Update my browser now