×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

How to combines values

How to combines values

How to combines values

(OP)
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.

RE: How to combines values

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.

RE: How to combines values

(OP)
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

RE: How to combines values

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.

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!

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