Need" last year totals" column after current column

[norbeckert]

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

 

[focwizard]

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:

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.

 

[norbeckert]

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

 

[focwizard]

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:

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

and, in your DEFINE:

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.