Hello,
I'm having a bit of logic issue when programming in WebFOCUS.
Does anyone know of a technique to capture the first value in the month (i.e. FIELD_1) and subtract it from all of the subsequent values in the month?

I know this is incorrect, but this is what I'm trying to accomplish:

TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FIRST_DAY_OF_MONTH/I3=FST.FIELD_1 WITHIN MONTH;
COMPUTE FIELD_2=FIELD_1-FIRST_DAY_OF_MONTH;
BY DATE
BY MONTH
END

Desired Output:

DATE.......MONTH...FIELD_1...FIRST_DAY_OF_MONTH_VALUE....FIELD_2
20070101...1.......100............................100.............................0
20070102...1.......200............................100.............................100
20070103...1.......350............................100.............................250
...
20070201...2.......120............................120.............................0
20070202...2.......250............................120.............................130

Any help is appreciated. Thanks!

------------
WebFOCUS 7.6.2
IBM DB2

This message has been edited. Last edited by: eb,

quote:

TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FIRST_DAY_OF_MONTH/I3=FST.FIELD_1 WITHIN MONTH;
COMPUTE FIELD_2=FIELD_1-FIRST_DAY_OF_MONTH;
BY DATE
BY MONTH
END

Here is 1 idea:

  
SET ASNAMES = ON
TABLE FILE EXAMPLE
SUM 
    FST.FIELD_1 AS 'BASE_AMT'
  BY MONTH
 ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE EXAMPLE
SUM 
    FIELD_1
  BY MONTH
  BY DATE
 ON TABLE HOLD AS HOLD2
END
-RUN

JOIN MONTH IN HOLD1 TO ALL MONTH IN HOLD2 AS J1
-RUN

TABLE FILE HOLD1
SUM
    MONTH
    BASE_AMT
    FIELD_1
   COMPUTE FIELD_2/P14.2C = BASE_AMT - FIELD1;
  BY MONTH NOPRINT
  BY DATE
END
-EXIT 

Hope this, and, the other's that follow, help...

Thanks Tom!
That seems to work, only problem is I'm working with somewhat large data sets, so holding and joining may hinder performance. Then again, I'm only dealing with a few fields so it shouldn't be all that bad.

------------
WebFOCUS 7.6.2
IBM DB2

eb,

"HOPE" it works, although, if there are multiple years involved, it won't.
For that, I would concat the year to the month, or, EDIT(DATE,'999999')...
and use that field as the key for your JOIN...

We ALL work with large volumes of data, it always needs to be presented in an efficient manner.
That's why we create VIEWs, FOCUS DB Files, or separate TABLEs altogether.
Also, is the presentation on a DASHBOARD, ad-hoc query, monthly report, etc.

These are factors everyone deals with when creating a production process.

I'm sure you'll figure out what's best for the client, AND, the most expedient way to present the data...

Good Luck!

How about this in one pass. The LAST keyword allows you to compare the value of a field on the current row to the value of the field on the 'last' row. Since you are sorting by DATE first that should take into account multiple years. When you go from December to January the month is changing and you don't need to test the year.

 TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FDOM/I3 = IF MONTH NE LAST MONTH THEN FIELD_1 ELSE LAST FDOM;
COMPUTE FIELD_2/I3 = FIELD_1 - FDOM;

BY DATE
BY MONTH
END