Joined: 30 Apr 2018
Location: United States
Online Status: Offline
Posts: 1
Topic: Conditional maximum 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" />
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
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.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
