How to combines values

[snarkie]

Hi There

Say I have a report that looks like this:

Month. Sales
------- ------
Jan 10
Feb 12
Mar 14
Apr 16

How would I dynamically calculate new rows that combine specific values. Like this:

Month. Sales
------- ------
Jan 10
Feb 12
Jan+Feb 22
Mar. 14
Jan+Feb+Mar 36
Apr 16

As you can see, I need to create a new month, call it whatever I choose, and specify which records to sum when it is calcuated. The data of course are way more complicated than this. I cannot use something like "row 1 + row 2" because everything is dynamic.

Any suggestions? I should mention that the obvious solution of creating new records in the source table is not an option.

Thanks in advance.

 

[focwizard]

The easiest way is to use a facility of WebFOCUS called FML (Financial Modeling Language). Without knowing anything about your data, nor what you want to do with it, it's tough to be specific, but, assuming you had 2 fields, 'MONTH.' and 'SALES', you would code something like:

TABLE FILE filename
SUM SALES FOR MONTH.
Jan LABEL JAN OVER
Feb LABEL FEB OVER
RECAP JANFEB = JAN + FEB; AS 'Jan+Feb' OVER
Mar LABEL MAR OVER
RECAP JANFEBMAR = JANFEB + MAR; AS 'Jan+Feb+Mar' OVER
...

The keyword 'FOR' triggers FML. Then you specify the values for each row, giving it a LABEL, for later reference in RECAP calculations.

 

[snarkie]

Hi focwizard!

Thanks so much for the answer. That is a step in the right direction. Unfortunately, the data are worse than that. I should have provided a better example. Here is a better idea.

Original report...

year Month. Sales
----- ------- ------
2001 Jan 10
Feb 12
2002 Jan 14
2003 Apr 16
2003 May 10

===============================

and I want...
year Month. Sales
----- ------- ------
2001 Jan 10
Feb 12
2002 Jan 14
2003 Apr 16
2003 May 10
2001+2002 Jan 24
feb 12


From the data source point of view, imagine there are records that look like this...

year...month...sales
2001 jan 10
2001 feb 12
2002 mar 14

It is as if I want to run this query on the data source.

INSERT INTO tblName (year, month, sales)
SELECT '2002-2001' as year, month, SUM(sales)
FROM tblName
WHERE (year = 2002 or year = 2001)
GROUP BY month


I was able to use your code when I have only 1 column with

SUM SALES FOR MONTH

But I am stuck since there are 2 cols. It should be something like

SUM SALES FOR YEAR AND MONTH

Any more suggestions?

Thanks

 

[focwizard]

I always have suggestions (whether they're useful or not is up to you).

Since you want to use YEAR and MONTH as your 'sort' field, can you create a DEFINE, which concatenates YEAR and MONTH into a single field, called something like YRMO? Then your request is: SUM YEAR MONTH SALES FOR YRMO NOPRINT
2001JAN OVER 2001FEB OVER ... You can then use YRMO in the RECAPs.