Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Help with COMPUTE
 Login/Join
 
Gold member
posted
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!
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Location: Westminster, CO | Registered: April 24, 2003Reply With QuoteReport This Post
<Don>
posted
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
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
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.
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
<Don>
posted
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.
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks, Don! This worked great!
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.