How to get a date one less than max

[wanzek1]

I need to return a date that is one date less than the max.

4/11/13
4/19/13
5/20/13
5/29/13

I would like to return the date 5/20/13. I cannot just subtract days from the max because the difference changes throughout each record.

Thanks!

 

[fisheromacse]

i am not sure of your desired outcome, but i think you can suppress the values you do not want to see by doing something like this:

-create a Summary of a field that is distinct to each record (ie: DTS). In the summary options, choose distinct count.


//Place in Section Expert - Suppress Formula for the details section.
(recordnumber<>(DistinctCount ({Table.DTS})-1))

If your items are grouped and you need this for each grouping, add that to the summary
(recordnumber<>(DistinctCount ({Table.DTS}, {Table.GroupedOnField})-1))


Apologies in advance for mistakes, typos or just plain not understanding the need.

 

[wanzek1]

Let me explain a little bit:

The date that I am trying to get corresponds to the previous meter reading on a piece of equipment. The table has a field for previous meter reading but there is no field for previous meter reading date. I need to return the date that corresponds with the previous meter reading. Which would be the date reading that is one less than the max date return. This cannot be done with a formula that subtracts a certain amount of dates from the max because the meter readings are not done on constant days.

Each one of the following Units are a row in the report (The fields under the unit are columns across the page). These are just 2 examples of over 5000 units.

Unit # 10110
Previous Meter Reading 3545
Previous Meter Reading Date 5/20/2013
Current Meter Reading 3547
Current Meter Reading Date 5/29/2013


Unit# 10215
Previous Meter Reading 2547
Previous Meter Reading Date 4/15/2013
Current Meter Reading 2467
Current Meter Reading Date 5/10/2013

The Current Meter Reading Date is the max of the field. However, I don't know how to get the previous meter reading date. I cannot suppress a bunch of rows.

 

[MCuthill]

wanzek1:

If you are okay with a reverse order of dates... you might be able to work with "Top N" functionality (Top "2" dates, sorted descending, if the theory is sound).

If the apporach worked, you would see:

Unit # 10110
Current Meter Reading 3547
Current Meter Reading Date 5/29/2013
Previous Meter Reading 3545
Previous Meter Reading Date 5/20/2013


Unit# 10215
Current Meter Reading 2467
Current Meter Reading Date 5/10/2013
Previous Meter Reading 2547
Previous Meter Reading Date 4/15/2013

I have not tested the idea, but I think it should work (assuming you are okay with the presentation change).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[wanzek1]

That is perfect! It worked great!

Thanks so much!

 

[MCuthill]

Excellent! Glad it worked for your needs.

Just as an aside (Should anyone else read the posts and above and question things):
I goofed on my presentation of the second one -- I assumed your original post had a "sort" on it and reversed the details of both without looking. When using this approach, it is just to say the subsections are in reverse order by date (more current first), regardless of what I may have posted to illustrate.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."