Prior Years data

[StanKorn]

I am trying to create a report that will give me comparative values for this year to date, last year (full year), and 2 and 3 years ago (full years). I need to look at revenue being generated by customer and need to be able to chart this data.
I am using CR 8.5, my data is in an ole db VFP 7 database and I have two primary data tables - FRMAST with current data and FRYMST with historic data. These 2 tables are linked to a Customer master by CustNo field.
Is thee a formulae that is useful in creating this data or will I have to translate the date field?

Basic Format is:
customer name sales this year LY 2yrsAgo 3yrsAgo

I want to subtotal by week, month qtr and year but first I need to get the data to report correctly.
Any help would be greatly appreciated
Styan

 

[synapsevampire]

Not sure what the data looks like, but a cross-tab might suit your purposes.

Insert->Cross Tab (place it in the report footer or Report Header).

When you place the date field in the Columns area, select it and click Group Options and select a column will be printed for each year. Add the Customer Name to the Rows area, and the number to sum in the Summarized Fields.

You don't have a lot of control over the look and feel, but the results should be accurate.

-k

 

[StanKorn]

Thanks, I'll try that.
Further to my original question, the data is in a VFP 7 DBF and information for date activity is collected in each record from start to finish - that is there is a load date, a deilivery date, a transaction (invoice) date and an invoice paid date all in the same line. There is also a total charges amount and a bal due amount as well as revenue codes for GL distribution. These are standard free tables. My difficulty is in generating code that will segregate years in the various date fields.
Can I somehow tell the report to look at the value in the date field and if the YR = 2001 then compare it to current date and place it in the 2 yrs ago column. Or can I look at the value in the date field and comparing it to current date use a minus 1 or minus 2 notation to do the calculation?
I have done this b4 in CR v7 but that was several yrs ago and it is frustrating me to no end. The crosstab is a good solution but I'm afraid it will not satisfy the powers that be when I get askedd specific questions re the report contents.
Thanks very much for your help,
Stan

 

[lbass]

It sounds like you want to do a manual crosstab, although I think a regular crosstab would probably work and be easier, too. You can use a formula something like the following to create columns, assuming a calendar year basis:

For {@yeartodate}:
if {table.date} in YearToDate then {table.amount}

For {@lastyear}:
if {table.date} in date(year(currentdate)-1, 01, 01) to date(year(currentdate)-1, 12, 31) then {table.amount}

For {@twoyearsago};
if {table.date} in date(year(currentdate)-2, 01, 01) to date(year(currentdate)-2, 12, 31) then {table.amount}

You would have to create the formulas for each field (total charges, balance due, etc.) you wanted to evaluate in these columns.

You can create groups (e.g., invoice number, customer number, etc.) and then summarize these formulas at different levels.

If I were you I'd try to insert a crosstab first and see if it met my needs, before trying the manual crosstab. If you are going to want to add percents of total or additional descriptive columns that don't fit the crosstab format then a manual crosstab is the way to go.

-LB

 

[StanKorn]

Thanks LB.
I'll let you know how I make out