Valuation Date Formula using 'Previous'

[acr1]

Hi All

I am trying to get the last valuation date using "previous" if the date is not the 30 June 2003.. I wish to display all dates that are the 30 June 2003 and if not then the previous valuation date.

I am using the formula as below which is working fine

({ASSDEPRE.DEPRECDATE}) = Date (2003,06,30)

and have tried using "previous" in both asc and desc sorts in an IF statement to no avail..

Any help as soon as is possible would be greatly appreciated,

TIA
Angus

 

[fredp1]

I would use you selection statement to limit the data upto the date that you want e.g. 30/06/03
Then in your report add a group on the deprecdate.
Insert a summary function of max on the deprecdate. This will get the maximum value of the deprecdate. It will be 30/06/03 or the last date before that

Fred

 

[acr1]

Hi Fred
Thanks for your prompt advice however it does not appear to be displaying previous dates..for records that are one date previous...

 

[fredp1]

Hi,

There is a previous() function, to be used but, can you post some sample data and expected results please.

Ta
Fred

 

[acr1]

Hi Fred
Once again thanks for your prompt reply..where are you geographically.?

The Formula is

If ({ASSDEPRE.DEPRECDATE}) = Date (2003,06,30)
then {ASSDEPRE.DEPRECDATE}
Else
Previous ({ASSDEPRE.DEPRECDATE})


which is not working properly as it extracts approx 4000 records aand I only want an extra 20 that are NOT 30 june 2003..

Some of the date data to be extracted is as follows:

31/03/2003
31/12/2002
30/11/2002
31/10/2002
30/09/2002
31/05/2002
30/04/2002
31/03/2002
30/06/2001
29/06/2001
1/07/2000
30/06/2000
29/06/2000
1/07/1999
30/06/1999

 

[fredp1]

Hi acr1,

I'm in located in Sydney. Where are you?

The expample isn't clear to me of the result that you want.
I'm assuming you want this...
Senario 1
If you had 3 dates say 21/006/03 , 26/06/03 and 30/06/03
then you want 30/06/06.

Senario 2
If you had 3 dates say 21/006/03 , 26/06/03 and 29/06/03
then you want 26/06/06.
(my previous solution assumed that you wanted the 29/06/03)

Okay... there is a cool function called NthLargest

create a formulae {@last Deprec Date}
if max({ASSDEPRE.DEPRECDATE}) = Date (2003,06,30)
then Date (2003,06,30)
else
NthLargest(2,{ASSDEPRE.DEPRECDATE});

Try that out

Fred
P.S.
I'd use a parameter or a variable to hold the Date (2003,06,30) value as it makes maintenance easier.

 

[acr1]

Hi Fred
Me well Wellington NZ and thanks again for your prompt response.

It did not like the 'max' as was wanting a field , time, string, booleen, etc.. but when running still brings out the whole db of 4000 plus recs.

Scenario 1 is correct.
Scenario 2 would be 29/06/2003.

I need 30/06/2003 or the last available date

TIA

 

[fredp1]

Hi ?

Sorry... max should be maximum() (mixup with SQL and Crystal)
The previous posting using maximum should be okay then?
If the results for scenario 2 would be 29/06/2003 then the maximum({table.field}) should give you the correct results, becasue the maximum in scenario 1 is 30/06/03 and
in scenario 2 its 29/06/03.
If you do not whant to display all the details suppress/hide the detail section

When you say i doesn't give the right reults please specify whats actually happening.

Thanks
Fred

 

[acr1]

Hi Fred

Is it possible to run what we have communicated.. in SQL .? and changing the 'deprecdate' parameters below.?

WHERE
COMPEQ."COMPKEY" = ASSVALUE."COMPKEY" AND
ASSVALUE."ASSVALKEY" = ASSDEPRE."ASSVALKEY" ANS
ASSDEPRE."DEPRECDATE" = '30 JUNE 2003'

TIA
Angus

 

[fredp1]

Hi Angus,

I see what the problem is now...

You have specifically specified in the where clause the date = 30/06/03.
This will only return records with that date or non at all.
(if you do not have the data in the dataset then maximum or previous will give you the results you expect)
If you have a less than equal to e.g
WHERE
COMPEQ."COMPKEY" = ASSVALUE."COMPKEY" AND
ASSVALUE."ASSVALKEY" = ASSDEPRE."ASSVALKEY" ANS
ASSDEPRE."DEPRECDATE" <= '30 JUNE 2003'

then the maximum function or even the Nth Largest will work.

Fred

 

[acr1]

Hi Fred

I inserted the line ASSDEPRE."DEPRECDATE" = '30 JUNE 2003'
as an example but it is not in the SQL WHERE statement behind the report..sorry about that.

I have tested ASSDEPRE."DEPRECDATE" = '30 JUNE 2003' and I know it works and only brings out 30 june 2003 data..

So I am back to testing your formula which still draws 4000 plus recs.

Current formula is as follows:

If maximum({ASSDEPRE.DEPRECDATE}) = Date (2003,06,30)
then Date (2003,06,30)
else
NthLargest(1,{ASSDEPRE.DEPRECDATE});

TIA
Angus

 

[fredp1]

Hi Angus,

getting there...

What exactly do you want to see and where?
If you donot want to see the deails suppress/hide them.
Place the formulae in a group or somewhere on the report where you want to see it.
You can create a group of Asset Id or sothing (not real sure what your trying to show)

Can you be more specify.

Thanks and hang in there.

Fredp
P.S.

The more info you can provide upfront the less threads we can have.
Just as a general advise, specify CR version, db type/version,
sample data and expect results.

 

[acr1]

Fred
CR 8.5, and Oracle 8i db
Based on other selection criteria (I dont think we need to go there) but as follows...

One
@DateNOtEqual
{COMPEQ.PURCDATE} <> {ASSVALUE.ACQDATE}

Two
@DateVariant
Abs ({COMPEQ.PURCDATE} - {ASSVALUE.ACQDATE})

Three
@datediff
{@DateVariant} > 366 //Greater than 1 year

I want to see all data for 30 June 2003 (which works) plus the last date for those recs that are not 30 June 2003 based on the above selection criteria

I have supplied prev sample data in this thread..for dates

Would like to resolve today if possible..as is urgent (if in fact it can be done)

TIA
Angus

 

[fredp1]

Angus,

Can you specify the tables that your using at what info they contain and which table the info comes from.

I can't see what else is going on as you asked a very specific question. Depending on what you say , will point to the answer.

Cheers
Fred

 

[acr1]

Fred

SQL as follows out of the crystal report..

SELECT
COMPEQ."AREA",
COMPEQ."COMPKEY",
COMPEQ."PURCDATE",
COMPEQ."UNITID",
ASSVALUE."ACQDATE",
ASSVALUE."CAPTLEXP",
ASSVALUE."EXPLIFE",
ASSVALUE."INITCOST",
ASSVALUE."REVALAMT",
ASSDEPRE."ACCDEPREC",
ASSDEPRE."DEPRECAMT",
ASSDEPRE."DEPRECDATE",
ASSDEPRE."WRITEDNVAL"

FROM
"IMSV7"."COMPEQ" COMPEQ,
"IMSV7"."ASSVALUE" ASSVALUE,
"IMSV7"."ASSDEPRE" ASSDEPRE

WHERE
COMPEQ."COMPKEY" = ASSVALUE."COMPKEY" AND
ASSVALUE."ASSVALKEY" = ASSDEPRE."ASSVALKEY"

ORDER BY
COMPEQ."AREA" ASC,
COMPEQ."COMPKEY" ASC,
ASSDEPRE."DEPRECDATE" DESC

HTH
TIA

 

[fredp1]

Do you want one line per compkey?

Is there one ASSVALUE line per COMPEQ or multiple.
It looks lines there is mutiple ASSDEPRE per ASSVALUE. Correct?

Fred

HTH?

 

[acr1]

1. Do you want one line per compkey?
- Yes

2. Is there one ASSVALUE line per COMPEQ or multiple.
- one ASSVALE

3. It looks lines there is mutiple ASSDEPRE per ASSVALUE. Correct?

- Correct Yes ...monthly depreciations..

TIA

 

[fredp1]

Hi Angus,
The problem that your experiencing is a 1 to many problem table linking problem.

There are many assdepre records for every assvalue.
Hence this is why you see the multiple records.

It can be fixed...

Create a group at the assvalue key, then you can insert a summary function, max.
Right click on the deprecdate , Insert Summary function, Maximum, (add it to the new group) on the footer of the ASSVALUE group.
The equivilent formulae is
maximum({ASSDEPRE.DEPRECDATE},(ASSVALUE.ASSVALKEY))
suppress/hide the details.
That should work.

The trick is you do not want to see the asset depreciation records.

Cheers
Fred

 

[acr1]

Fred
I agree that there are multiple deprec recs..and should have been more specific at the start..

I think I will rewrite the query or try SQL..our thread seems to be getting out of hand.

Thanks for your input anyway.

 

[fredp1]

Hi Angus,

If it makes its any better, lost of others had problems with the 1 to many issues too. I've either used subreports and passed back shared variables to achieve it, or using variables to get the data at the right level of detail or a view in the db with summarised subqueries..
There are lots of threads on this area and it is a difficult one at best of times. Even in SQL you'll need to do some sort or aggregation to avoid the details or a maybe a subquery can be more effective.

Good luck

Fred