Prior Year Formula

[mrtanner]

Hi Guys,

Got another problem with prior year formulas. I am using an Oracle 9 database with Crystal Reports XI. This is the formula I am trying to edit but with no success:-

select
d.descr as department,
n.refno as bill,
m.fileid,
c.name,
c.nclient,
m.shortdescr,
au.name as author,
(n.fees + n.disb + n.antd + n.sund + n.tax) as original,
(n.fees_paid + n.disb_paid + n.antd_paid + n.sund_paid + n.tax_paid) as received,
(n.fees + n.disb + n.antd + n.sund + n.tax -
n.fees_paid - n.disb_paid - n.antd_paid - n.sund_paid - n.tax_paid) as debt,
n.dispatched,
DECODE(TRUNC(sysdate)-TRUNC(N.DISPATCHED)-120-ABS(TRUNC(sysdate)-TRUNC(N.DISPATCHED)-120),0,120,FLOOR((TRUNC(sysdate)-TRUNC(N.DISPATCHED))/30)*30) AS AGE
from
empdept d, employee au, client c, matter m, nmemo n
where
m.author = au.code and
m.nclient = c.nclient and
m.nmatter = n.nmatter and
AU.DEPT = D.CODE(+) AND
N.DISPATCHED < TRUNC(sysdate)+1
and (n.fees + n.disb + n.antd + n.sund + n.tax -
n.fees_paid - n.disb_paid - n.antd_paid - n.sund_paid - n.tax_paid) <> 0
AND (RV_TYPE = 'N' OR RV_TYPE = 'X')
AND M.DEBTORS <> 0

This give me our debtors as at the current date and split up into 30/60/90/120 days outstanding.

What I want is exactly the same for the previous year. I have tried TRUNC(SYSDATE-365,'YYYY'), plus other formulas that I have found on the net ,in place of the existing sysdate but no go. I had previously got the correct formula for prior fiscal year but this is of no good to me here as I want the actual total of debtors not just for the year.

Has anyone got any ideas??

Any help is appreciated .

Thanks

Mr Tanner

 

[mrtanner]

Has anyone got any ideas for me????

Mr Tanner

 

[Tatertot45]

since no one else has got anything for you, i'll give you a thought. I pasted in a formul that i use for Last year-Year-to-Date calculations. It is an If/then, but the general idea might help. Basically, Inv-date is a date field and i supbtract 1 from that year. so... If the year of the invoice date equals the current year minus 1, then display the @True Sales, else 0. I then accumulate those sales with a running total. not sure that will work for you, but maybe using Year(sysdate)-1 in some fashion might help.

if (Year({inv_hdr.inv-date}) = (Year(CurrentDate)-1)) then {@True Sales}else 0

 

[Tatertot45]

... Just had another thought. sometimes I use the record selection to help me. It has a "Period" selection. There are two of interest here, YTD and LastYTD. I do this.

{inv_item.sales-acct} in ["40010"] and
{customer.slsman} in ["chrisK"] and
({inv_hdr.inv-date} in LastYearYTD or
{inv_hdr.inv-date} in YearToDate)

Sales-acct is one tag i lok for in record selection, and so is Slsman. I then use "or" and parens to make the "statement" Invoices in the periods of YearToDate OR LastYTD. so i capture all the invoices in both periods, becuase i surounded them with Parens.

MAke sense? Good luck...

 

[beltmanjr]

I have no oracle installed here, but would advise you to simplify the whole thing and first of all test what

select TRUNC(SYSDATE-365) from DUAL

gives you and work from there.

Also check

http://www.psoug.org/reference/date_func.html

It describes a way of adding months
ADD_MONTHS(<date>, <number of months_integer>
SELECT add_months(SYSDATE, 2) FROM dual;

perhaps there is an add year function to, but 12 months still equals 1 year

 

[mrtanner]

Tatertot45,

Unfortunately I don't need a YTD figure but a figure of the debtors as at the same time in the previous year.

Thanks for offering your help and I will keep those formulas in mind for future reference

Mr Tanner

 

[mrtanner]

beltmanjr,

Thanks for your answer. Yes I think that may be the best solution, simplify and work from there. I will check out the link you provided with interest.

Thanks again

Mr Tanner