Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005-2015 : Report Design
Message Icon Topic: Conditional maximum Post Reply Post New Topic
Author Message
Lowe
Newbie
Newbie


Joined: 30 Apr 2018
Location: United States
Online Status: Offline
Posts: 1
Quote Lowe Replybullet 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" />

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

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

Edited by Lowe - 11 May 2018 at 4:43am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3633
Quote hilfy Replybullet 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
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.046 seconds.