Max Date and Previous to Max Date, How do I?

[rmiller11]

I am doing a wage review report. I need to seeon one line per employee the Effective date or their last rate change which may or may not have been an increase. Next column I want to find from the last effective of a rate increase the difference between the Most recent increase to the prior wage rate and then the date of that increase.

I know how to get the max date but am not sure how to do the rest. I guess the rest of it would have to put in a subReport? Still I am not sure of the formulas.

This is a hard one

 

[FVTrainer]

To get a good response, you generally need to show what your data looks like and what result you want to see, rather than describing it. Descriptions are good, but without seeing what your data looks like, it's hard to come up with the right solution.

 

[lbass]

I think you could do this by first sorting by {table.effectivedate} ascending. Then create two formulas:

{@preveffdate}:
previous({table.effectivedate})

{@wagediff}:
{table.wage} - previous({table.wage})

Place these in the details section, and then go to format-> section->details->suppress->x+2 and enter:

{table.effectivedate} <> maximum({table.effectivedate}, {table.employeeID})

This assumes that you have a group on {table.employeeID}.

-LB

 

[rmiller11]

I have a problem with the formula that you suggested and I think it is because the beginning people in the report only have one record. What kind of if statement do I use with the Previous function?

 

[lbass]

Good point. Try adapting the formulas like this:

{@preveffdate}:
if {table.effectivedate} <> minimum({table.effectivedate},{table.employeeID}) then
previous({table.effectivedate}) else Date(0,0,0)

{@wagediff}:
if {table.wage} <> minimum({table.wage},{table.employeeID}) then {table.wage} - previous({table.wage}) else 0

Place these in the details section, and then go to format-> section->details->suppress->x+2 and enter:

{table.effectivedate} <> maximum({table.effectivedate}, {table.employeeID})

-LB