Maximum Effective Date

[deborahyr]

What formula do I use to get the minimum effective date of a job in a subreport created to capture job history? This job history report pulls all rows regardless if a change in job occurred (a new row is created if the dept reorged, etc).
Fields: EffDate, Job Title, Department, Organization

01/01/2000, Admin Asst, HR, AHR
05/02/2000, Admin Asst, HR, Atlas HR
08/31/2001, Secretary, AIS, Atlas IT

Result:
01/01/2000, Admin Asst, HR, AHR
08/31/2001, Secretary, AIS, Atlas IT

 

[DataDivaJill]

sort on the date, and do a running total to count the record by date. then format the section to hide if count <> 1
Jill

I usually do this for showing the most current history item.

 

[deborahyr]

This works perfectly but I'd like to get the first start date of the job. How would I do that?

 

[lbass]

Insert a group on job title, sort by date, and then drag the detail fields into the group header and suppress the detail section.

-LB

 

[deborahyr]

I did as suggested but still have the same result - it's not capturing the first record for job title. I grouped by job title ascending (default) and then sorted by date descending but the grouping trumps sorting. It's almost as if I need to sort my running total in descending order to get it to work. Any more suggestions?

 

[lbass]

What running total?

Do you have some other groups? Groups always take priority for sorting. If you only have a group on job title and then you sort by date ascending (not descending), you will automatically get the correct info in the group header.

-LB

 

[deborahyr]

The first respondent suggested doing running total. Even when I delete the running total and create the group by Job Title and sort on Date, it does not do what I need it to do.
My dilemma with this is that it sorts the job titles(asc) first and then the dates. I need it to read like a resume with most recent date to earliest regardless of job title name. Is there a way to do this?

 

[lbass]

Okay, now I see. I would delete the group, sort on date ascending and then go to the section expert->details->suppress and enter:

{table.jobtitle} = previous({table.jobtitle})

-LB

 

[deborahyr]

EXCELLENT!! Thank you very much - I guess I should have been a little more clearer orginally. I actually used {table.jobtitle} = next({table.jobtitle}) so that I can get descending dates.