Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with time formula

Status
Not open for further replies.
Apr 18, 2007
209
US
Hi friends Need help on below, I have records like this

T1 abc1 10/20/1008 8.00.00
T1 abc3 10/20/1008 10.00.00

I want to show this as

t1 abc1 abc3

Basically i like to min and max of recirds for the time stamp.

Max(column2,datetime), etc
Any help on this?
 
Well, why not do that? Insert a group on datetime, and then insert minimum and maximum on field 2 at the group level.

-LB
 
LB only problem how will it know min and max on string field.I did other way i did difference on the currenttimestamp and datetime field and converted the difference value to text and attached into colum 2 totext(@diff)+" "+field2 which is a string. Then i was able to do min and max since it has diff value. Then i create formula to remove diff value mid(field,inst(field," "). This works, but I was wondering if there is other short cut way.
 
You CAN insert minimum and maximum on a string field.

-LB
 
I think toblerone only wants to see record where date is Max.

Group on whatever field is T1

In section expert open formula on suppress detail

Datefield <> Maximum(datefield, T1Field)

Careful though this only suppresses data if you want to do any total etc you will need to use running total and evaluate sums using reverse condition.

Datefield = Maximum(datefield, T1Field)

Another way is to use same grouping, and then sort data based on datefield descending, move all your records to group footer and suppress details.

Still need to use RTs, but now evaluate on cahnge of Group.

Ian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top