I'm trying to create a computed field and not having any luck. I created an example of what I'm trying to do with the CENTFIN file.

TABLE FILE CENTFIN
SUM VALUE
BY YEAR SUBTOTAL
BY ITEM
ACROSS QUARTER
END

What I want is to have a column for each QUARTER that calculates the percentage of total ITEM value within YEAR. For example, total VALUE of RProduct for Quarter 1 in Year 1998 is 51.4% of the total value in Year 1998 (40,025,618/77,806,079). I've been banging my head all weekend and I can't get it. Any ideas would be greatly appreciated. Thanks!

Try this:

TABLE FILE CENTFIN
SUM VALUE
COMPUTE MYPCNT/D6.2% = 100*(ITEM/YEAR) ; AS 'My Percent'
BY YEAR SUBTOTAL
BY ITEM
ACROSS QUARTER
END

Although I did not test this code, it's very similar to something I've done in the past.

Try it and let me know if that works for you.

Thanks... Simon

There is a way to get what you want without computes. WebFOCUS has a featured called WITHIN and using this with the prefix PCT will give you what you want. See code below:

TABLE FILE CENTFIN
SUM VALUE PCT.VALUE/D12.2% WITHIN YEAR
BY YEAR SUBTOTAL
BY ITEM
ACROSS QUARTER
END

Thanks, guys, but actually, neither of those ideas work. In the first example, the fields item and year aren't numerical fields so you can't do division on them, so I'm guessing what was meant was "sum of value by item divided by sum of value by year"? But I'm not sure how to code that.

In the second example, it doesn't return the right values for the percent columns. Intead of each quarter adding up to 100%, the sum of the four percent columns adds to 100%. Also, just as an FYI, in WF 436, the Report Painter does not support the WITHIN syntax, so if you manually code it, then open the fex in the Painter it blows the code away! I was told that this was fixed in 5.2.

The following should produce what you want. However you need to know your data, in this case its obvious there are 4 quarters. The trick is to get the totals for each quarter and then to a Multi Display Sort Fields.

DEFINE FILE CENTFIN
VALUE1/D12.2=IF QUARTER EQ 1 THEN VALUE ELSE 0;
VALUE2/D12.2=IF QUARTER EQ 2 THEN VALUE ELSE 0;
VALUE3/D12.2=IF QUARTER EQ 3 THEN VALUE ELSE 0;
VALUE4/D12.2=IF QUARTER EQ 4 THEN VALUE ELSE 0;

END
TABLE FILE CENTFIN
SUM VALUE1 NOPRINT VALUE2 NOPRINT VALUE3 NOPRINT VALUE4 NOPRINT BY YEAR NOPRINT
SUM VALUE COMPUTE PCTVALUE/D12.2% =IF QUARTER EQ 1 THEN VALUE/VALUE1*100
ELSE IF QUARTER EQ 2 THEN VALUE/VALUE2*100 ELSE
IF QUARTER EQ 3 THEN VALUE/VALUE3*100 ELSE
IF QUARTER EQ 4 THEN VALUE/VALUE4*100 ELSE 0;
ON TABLE NOTOTAL
BY YEAR SUBTOTAL
BY ITEM
ACROSS QUARTER
END


Note: the ON TABLE NOTOTAL ... this is included because the table total will equal 500% sinced there is 5 years to get around that you would need to do a ON TABLE RECAP and SUBFOOT and recalculate all the column percentages. Also Note ON TABLE RECAP is not supported in painter (it will be in the future). Workaround is to DEFINE a CONSTANT field - make that a your first BY with a NOPRINT and RECAP on the CONSTANT field.

Thanks, Don! This worked great!