×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need" last year totals" column after current column

Need" last year totals" column after current column

Need" last year totals" column after current column

(OP)
Hi All,

I'm brand spanking new to WebFocus and need some direction please.  I have a report accepting date parameters that sums up the sales by product group and displays two columns.  It works fine.  I would like to include the totals for the previous year in the same time frame in an additional column as shown below:

Item group    Total   Last Year Totals
----------    -----   ----------------
2             $10,000  $24,000
35            $8,500   $7,500
9             $6,000   $6,400

I am unclear on how to approach this.  I was thinking of using two hold files - 1 to hold the item group and current totals and then run the query again to get last years totals for the same groups, then join the files to create a final report?

I am using version 7.1 of Developer Studio.  I am not familiar with the underlying code and I noticed that almost all responses refer to FOCUS code.  Yikes!  

TIA,

Norb

RE: Need" last year totals" column after current column

Norb,

You COULD use your approach, but that would require two passes of the data, and then the 'merge' step. Let me suggest an alternative.

I'm guessing that you screen on the date, to get this year's data, in the TABLE protion of your request. This means you ONLY retrieve this years data. As an alternative, use the screening in a DEFINE. For example:

CODE

DEFINE FILE filename
THIS_YEAR_SALES=IF YEAR EQ '2006' THEN SALES ELSE 0;
LAST_YEAR_SALES=IF YEAR EQ '2005' THEN SALES ELSE 0;
END

and, in your request, add selection so you get only this year and LAST year. Then, you can reference the DEFINEd fields as if they were database fields.

This way, anything that contributes to last year, goes into the LAST_YEAR_SALES bucket, and anything for this year goes into the THIS_YEAR_SALES bucket.
You only pass the data once, and only retrieve records pertaining to this year or last year.

RE: Need" last year totals" column after current column

(OP)
Hi focwizard,

Thank you for your prompt reply.  You are right on track with the date screening and your answer looks like it will work.  

I would like to extract the year from the &DTEND parameter that is passed to the report, store it into a DEFINE variable and use it in the right side of the comparison (YEAR EQ CURRYEAR THEN... and YEAR EQ CURRYEAR - 1 THEN...).  However, I am unclear on how to get the year from an amper variable whose value is equal to "Jan 31 2006".  Can you assist here as well?

Thanks,

Norb

RE: Need" last year totals" column after current column

Norb,

Well, there are a few things to consider. If &DTEND is passed in as 'MMM DD YYYY', then a simple EDIT can extract the year into another Dialogue Manager variable. Then subtract 1 to get 'last year' into another variable, and use THEM in the DEFINE and TABLE. It might look like this:

CODE

-SET &DTYEAR = EDIT(&DTEND,'$$$$$$$9999');
-SET &LSTYEAR = &DTYEAR - 1;

and, in your DEFINE:

CODE

THIS_YEAR_SALES=IF YEAR EQ '&DTYEAR' THEN SALES ELSE 0;
LAST_YEAR_SALES=IF YEAR EQ '&LSTYEAR' THEN SALES ELSE 0;

if those are the only two years of concern, you might want to add the selection to the TABLE request, if possible, so you only retrieve those TWO years' data.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close