pulling out a date from a list

[jschill2628]

I am not sure if this is possible.....

Example:

Reserves Transaction Date
$100,000 04/24/2006
$50,000 04/24/2006
$0 03/16/2007
$0 06/18/2007
$2,900,000 07/02/2007
$0 08/27/2007
$0 10/08/2007
$-3,000,000 10/15/2007
$0 10/15/2007
$50,000 10/16/2007
$50,000 10/16/2007
$-100,000 10/16/2007
$2,212,500 10/25/2007
$10,000 11/16/2007
$0 11/19/2007
$0 02/21/2008
$0 03/21/2008

From the above example, I would like to know the transaction that happened before the last one. So I would like to write a formula that would pull out the date (for this example) 10/25/2007, essentially the transaction that happened before the last one. Can you help me?
Thanks in advance

I am using Crystal XI. Thank you!

 

[Madawc]

From your example, you seem to want the the last non-zero transaction before the last one.

If you can exclude the zero transactions - which could be done in record selection - then you could do a summary count for the group and also a running total for the group. The record you want would then be the record where the running total is 2 less than the summary total.

This won't work if zero and non-zero transactions are mixed, you'd need to do a subreport, which will be slow and awkward. I can see no better way.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Create a formula {@nonzerodate}:

if {table.reserves} <> 0 then
{table.date}

Then create a second formula to capture the date:

nthlargest(2,{@nonzerodate})

If you are doing this at some group level, then use:

nthlargest(2,{@nonzerodate},{table.group})

-LB

 

[jschill2628]

That worked PERFECT! Thank you so much. I have another question....

If I wanted to graph my reserves...actually graphing the sum of the reserves. Is there a way to graph or "show" a vertical line for the transaction date (for this example 10/25/2007).


l l
l l__________
l ________l
l_____l l
l_____________l___________

 

[lbass]

How would the graph be set up (graph type, on change of field, show value field)?

-LB

 

[jschill2628]

Okay I know haw to get the date on there, now I just don't know how to get rid of the zero's within that column. I created another formula:

OBL1:
if {@reserves} <> 0 then {CHITRANS.TRANS_DT}

OBL2:
nthlargest(2,{@OBL})

OBL3:
if {CHITRANS.TRANS_DT} = {@OBL2} then 3500000

I created a chart with Transaction Date in the "On change of" bucket and Reserves in the "Show value(s)" bucket. I also put OBL3 in the "Show value(s)" bucket; this gives me a great graph except for the value of OBL3 has all zeros except for the value I have placed if the transaction date equal OLB2 (it looks like a tent).

Can you help me to get rid of the zeros, so it will look like straight line? Thanks

 

[lbass]

Create a formula {@null} by opening and saving a formula without entering anything. Then change your Obl3 formula to:

if {CHITRANS.TRANS_DT} = {@OBL2} then
3500000 else
tonumber({@null})

-LB