I am _very_ new to Web Focus. The report I am trying to modify incorrectly counts months if the data range selected is more than 12 months.

In the sample below the "Number_of_Periods" field always shows 12 months if the date range is 12 months or more. I tried to create a modified version of that field (commented out), but is hasn't work.

The field "PERIOD" is a date field from the database. I am trying count the number of months based on PERIOD being limited by what date range is selected.

Any suggestions woudl be much appreciated.

DEFINE FILE SECURITY_POSITION

YearMonthI/MYY=PERIOD;
END

DEFINE FILE H1
Number_of_Periods/I11=IF Month EQ LAST Month THEN 0 ELSE 1;
MonthCount/I11=IF YearMonthI EQ LAST YearMonthI THEN 0 ELSE 1;
END

TABLE FILE H1
SUM
-*MonthCount
Number_of_Periods
MARKETVALUE
BY GL_COMPANY AS 'GL_COMPANY'
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &OUTPUT OPEN NOBREAK
ON TABLE HOLD AS H2 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

DEFINE FILE H2
Col1_Descr1/A30='Number of Months';
END
TABLE FILE SBEQY12H2
PRINT
-*MonthCount AS 'Total Periods'
Col1_Descr1 AS ' '
Number_of_Periods AS '
END

TABLE FILE H2
PRINT
-*MonthCount AS 'Total Periods'
Col1_Descr1 AS ' '
Number_of_Periods AS ' '
END

This message has been edited. Last edited by: <Kathryn Henning>,

Frist question
What is in the database...can you give some examples of rough data
So

Period    marketvalue gl_company
20120315  12345.       Abc
20120316  33421.       Abc
20120413  12332.       Abc
20120317  32112.       Bac
Etc

Then bases on this data create an exemple report

Glcompany numbermonths amount
Abc.       2.           Som
Bac.       1.           Som2

This is it?

This should be very easy.

Did you get any training and or documentation?

When you mentioned "PERIOD being limited by what date range is selected."

So, based on that i assume you have 2 parameters for START_DATE and END_DATE.

Using DATEDIF function you can find the months

NO_OF_MONTHS/I8 = DATEDIF(&START_DATE, &END_DATE, 'M');

I have some documentation (bought some books) but have had no training. I am trying to get my employer to pony up for that, but until then...

This is what the output of the report looks like right now:
NOVEMBER 1, 2011 - DECEMBER 31, 2012

Year Month Market Value
2011 November $80,000.00
2011 December $70,000.00
2012 January $60,000.00
2012 February $90,000.00
2012 March $25,500.00
2012 April $10,000.00
2012 May $80,000.00
2012 June $70,000.00
2012 July $60,000.00
2012 August $80,000.00
2012 September $70,000.00
2012 October $60,000.00
2012 November $90,000.00
2012 December $25,500.00
*TOTAL
***************
Number of Months 12
Average Market Value $72,583.33
As you can see, the number of months should be 14, but shows up as 12 instead. I tried the following, but got 0 for the number of months:

DEFINE FILE SECURITY_POSITION

YearMonthI/MYY=PERIOD;
END

DEFINE FILE H1
Number_of_Periods/I8 = DATEDIF(&FROMDATE, &THRUDATE, 'M');
END

TABLE FILE H1
SUM
-*MonthCount
Number_of_Periods
MARKETVALUE
BY GL_COMPANY AS 'GL_COMPANY'
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &OUTPUT OPEN NOBREAK
ON TABLE HOLD AS H2 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

DEFINE FILE H2
Col1_Descr1/A30='Number of Months';
END
TABLE FILE SBEQY12H2
PRINT
-*MonthCount AS 'Total Periods'
Col1_Descr1 AS ' '
Number_of_Periods AS '
END

TABLE FILE H2
PRINT
-*MonthCount AS 'Total Periods'
Col1_Descr1 AS ' '
Number_of_Periods AS ' '
END

DATEDIFF function works with smartdates, and I am not sure your from date is a smartdate format field.
You started with this
DEFINE FILE SECURITY_POSITION

YearMonthI/MYY=PERIOD;
END
That's good...
Now

TABLE FILE SECURITY POSITION
SUM MARKETVALUE
COMPUTE NR=1;
BY GL_COMPANY
BY YEARMONTHI
ON GL_COMPANY SUBTOTAL NR MARKETVALUE
END

THis should give you the result, maybe not exactly in the format you wanted, bit one step first.

Give this a try

Would the following be sufficient?

TABLE FILE SECURITY POSITION
SUM
CNT.DST.YEARMONTHI
MARKETVALUE
BY GL_COMPANY
END

CNT.DST counts the number of distinct values within a field

Thanks for the feedback. I will give those suggestions a try and post back with results once our server is back up. They decided to do maintenance this morning.

quote:

TABLE FILE SECURITY POSITION
SUM MARKETVALUE
COMPUTE NR=1;
BY GL_COMPANY
BY YEARMONTHI
ON GL_COMPANY SUBTOTAL NR MARKETVALUE
END

WHAT is NR? Is that supposed to be defined above? Please forgive my neophyte questionl.

NR is just a counter for your report and it is defined by the compute statement.
Did you try this ?

Yes I did, I ended up getting a meassage that no records were returned, so I am assuming I did something wrong and caused the report to error out.

This is what I tried:

TABLE FILE SECURITY_POSITION
SUM
MARKETVALUE
COMPUTE NR=1;
BY GL_COMPANY AS 'GL_COMPANY'
BY YEARMONTHI
ON GL_COMPANY SUBTOTAL NR MARKETVALUE
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &OUTPUT OPEN NOBREAK
ON TABLE HOLD AS SECURITY_POSITION
FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END

Can you post the master file of your database secutiy_position?
There seems something wrong in this , maybe your period field.

This is how the top part of our report file looks, does this help?

JOIN BORGELT_SECURITY_POSITION.BORGELT_SECURITY_POSITION.SECURITYID
AND BORGELT_SECURITY_POSITION.BORGELT_SECURITY_POSITION.BORGELT_PERIOD IN
BORGELT_SECURITY_POSITION TO UNIQUE
BORGELT_SECURITY_MASTER.BORGELT_SECURITY_MASTER.SECURITYID
AND BORGELT_SECURITY_MASTER.BORGELT_SECURITY_MASTER.BORGELT_PERIOD IN BORGELT_SECURITY_MASTER
AS J0
END
JOIN
BORGELT_SECURITY_POSITION.BORGELT_SECURITY_POSITION.PORTFOLIO IN BORGELT_SECURITY_POSITION
TO UNIQUE BORGELT_PORTFOLIO_MASTER.BORGELT_PORTFOLIO_MASTER.PORTFOLIO
IN BORGELT_PORTFOLIO_MASTER AS J1
END
JOIN
BORGELT_PORTFOLIO_MASTER.BORGELT_PORTFOLIO_MASTER.GL_COMPANY IN BORGELT_SECURITY_POSITION
TO UNIQUE GL_COMPANY.GL_COMPANY.GL_COMPANY IN GL_COMPANY AS J2
END
DEFINE FILE BORGELT_SECURITY_POSITION
UnitCost/D6.2=BOOKVALUE / UNITS;
UnRealGL/D18.2B=MARKETVALUE - BOOKVALUE;
date1/YYMD='&FROMDATE';
date2/YYMD='&THRUDATE';
datefrom/TRMDYY=date1;
datethru/TRMDYY=date2;
issuetype/A40=
IF ISSUETYPECODE EQ 220 THEN 'COMMON STOCK'
ELSE IF ISSUETYPECODE EQ 225 THEN 'AMERICAN DEPOSITORY RECEIPTS'
ELSE IF ISSUETYPECODE EQ 5 THEN 'CASH AND EQUIVALENTS'
ELSE IF ISSUETYPECODE EQ 230 THEN 'FOREIGN MUTUAL FUNDS'
ELSE IF ISSUETYPECODE EQ 231 THEN 'DOMESTIC MUTUAL FUNDS'
ELSE 'NOT DEFINED';
MonthI/M=BORGELT_PERIOD;
Month/A20=
DECODE MonthI( 01 'January'
02 'February'
03 'March'
04 'April'
05 'May'
06 'June'
07 'July'
08 'August'
09 'September'
10 'October'
11 'November'
12 'December'
ELSE 'Unknown');
YearI/YY=BORGELT_PERIOD; -*ALH 2/27/2013
YearMonthI/YYM=BORGELT_PERIOD
Year/A20=YearI
END
TABLE FILE BORGELT_SECURITY_POSITION
SUM
YearI AS 'Year'
Month
MARKETVALUE/D12.2CN AS 'Market Value'
BY GL_COMPANY_DESC NOPRINT
BY YearI NOPRINT
BY MonthI NOPRINT

There is a lot to discuss about this code.
But first...where are the master files?

1) Use of the function DECODE might help you instead of all the if then else codes.
2) some formulas on dates are not needed. The month name is a format setting
3) formula year/a20=jear1; what should this do?
4) then the table file...
You are summing a year and month field and a MARKETVALUE
The last one will of course give you a result, but the others?
Suppose your data would be this
Year m value
2012 4 500
2012 5 300
2011 7 300
2011 8 400
The sum would give....
8046 24 1500

But I do not think that is what you want.

So...try my earlier piece of code...try to Understand it....and run it...


Try and see what you get.

And beg your management for a training. 3 days might already be a big step forward.

Yeah, I basically told them I am not going to be usefull for anything other than cosmetic changes without training.

WebFOCUS code and structure is very different from other languages I have worked in. Althought it could be that I am just being exposed to bad coding examples! (I did not write this report, I am just trying to fix it!)