Print Page | Close Window

Conditional maximum

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22595
Printed Date: 29 Mar 2024 at 3:37am


Topic: Conditional maximum
Posted By: Lowe
Subject: Conditional maximum
Date Posted: 10 May 2018 at 12:27pm
I work in a setting where an individual employee's salary (HourlyRate) can move within or above the salary range for the job title. The salary range for the job also moves over time.
In the attached image I display a data set showing one employee's salary progression over time relative to the range. I only show the top end of the range--JobMax. I created the dummy variable "AtMax" to indicate whether HourlyRate is at least equal to JobMax.
My objective is to find the earliest instance for which all successive instances have an HourlyRate that is at least equal to JobMax. Another requirement involves Sequence: there can be multiple actions (ActionID) on the same date, and I have sorted the data first by date, then by "Sequence" which places the most recent of the same-day actions at the top. When there are multiple actions on the same day, I need to exclude all but the last sequence for each day, such that any of the earlier sequences are not considered when evaluating for (current >= JobMax).
In the example here, I need the date 07/02/2005 returned, corresponding to ActionID 20971.

Thanks for any ideas on how to do this![IMG]https://drive.google/file/d/1T_TCzYUagHLw6pV5WCKrlVHKUNez-sRL/viewusp=sharing" />

https://drive.google.com/open?id=1T_TCzYUagHLw6pV5WCKrlVHKUNez-sRL

Please respond with any questions or suggestions if my post is unclear.



Replies:
Posted By: hilfy
Date Posted: 23 May 2018 at 8:16am
There is no way to meet this requirement just by linking tables in Crystal. Instead, you'll have to write the query yourself and use that in a Command to determine the earliest instance. For information about how to work with Commands in Crystal, see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

To show just the most recent "sequence" for each date, here's what you can do:

1. Group by date as your inner group. Make sure that you have it evaluating for each date.

2. Sort by sequence.

3. Put the data in the group header section instead of in a details section. This will show only sequence 1 for the date. If you really need the max sequence for the data and not the minimum sequence, you can put all of the data in the group footer or you can sort by sequence descending and put the data in the group header.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics



Print Page | Close Window