Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Variance Analysis
 Login/Join
 
Platinum Member
posted
I have 3 snapshots (taken at three different dates) of the same datasets. I want to do a variance analysis on these three to look for trends in the policy counts and amounts. What would be the best strategy to bring these datasets together to compare?

Dan Moyer
Webfocus 7.1.1


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
Dan,

Please post the masters and a mockup of what you want the report to look like.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
I stand corrected we are extracting hold files from these data sets for the three time periods.


period 1 Period 2 " Period 3 " Diff-cnt DIFF-amt ▲ ▼ %chg cnt %chgamt

sum cnt.plancode
current amount
BY PLANCODE RATESCALE AGE



This is as close to the lay out as I can project.

THe fields for each datset are:

Plancode
ratescale
age
cnt.plancode
curramount


Thanks

Dan

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


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
Dan,

I could use a bit more information but I'll go with what I have.

Assuming that the 3 hold file fields have the same formats and that the periods are stored in the files (if they are not, you can add them with a define), you can use Universal Concatenation (MORE) to put the files together.

Then you can use a BY, ACROSS, and COMPUTE to calculate the variances.

This is a simple example using the CENTORD demo table.

TABLE FILE CENTORD
SUM LINE_COGS
COMPUTE VAR1/F6.2=((LINE_COGS-LAST LINE_COGS)/LAST LINE_COGS)*100;
BY PLANT
BY YEAR
BY QUARTER
WHERE YEAR GT 2000
ON TABLE HOLD
END
TABLE FILE HOLD
SUM LINE_COGS VAR1
BY PLANT
BY YEAR
ACROSS QUARTER
END

Let me know if you need more info.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
That worked great. Is there some how you can issue the "MORE" code with painter. When I try to open the report it gives me a parsing error


Dan

wf 7.1.1

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


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
If you want to paint the report, save the contents of the MORE into a hold file and then open the painter and select that hold file. Or copy styling from another program.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
Ok thanks, I was hoping for a more stream lined approach like putting the "more" statement in a dialouge manager module or other. It looks like another case of having to massage the code.

Thanks

Dan


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
Open a New Feature Request (NFR) with IBI CSS. Sounds like a good idea.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
Ginny

I am having a second problem with doing this across thing.

I get the output I want, and then some.

The problem is I get a DIFFCNT column for the first period, and it is computing of the last row. Of course I only need the DIFFCNT to show up in the second and third period. Here is my code. I will give you only the related area.

TABLE FILE ANSRT066
PRINT
CFCURAMOUNT
PERIOD
BY POLICYNUMBER
BY BASICPLANCOD
BY BASICRATESCA
BY RISSUEAGE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS ANSRTALL
MORE
FILE ANSRT126
MORE
FILE ANSRT706
END
TABLE FILE ANSRTALL
SUM
CNT.BASICPLANCOD AS 'COUNT,BASICPLANCOD'

COMPUTE CNTDIFF/I5 = CNT.BASICPLANCOD - LAST CNT.BASICPLANCOD;
CFCURAMOUNT
BY BASICPLANCOD
BY BASICRATESCA
BY RISSUEAGE
ACROSS PERIOD
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML

Help Please

Dan

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


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
Just a quick idea. What you can do is take that last TABLE and put it in a hold file. Put an APP HOLD dirname right in front of it for the time being so that you can see the master it generates. Each of the ACROSS fields will have special names that you can then use in another TABLE request. In that request, you will just print the columns that you want with the BYs. You won't need the ACROSS. Of course, you will have to play with the headings a bit.

Let me know if this is enough information or if you want me to mock up an example for you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Platinum Member
posted Hide Post
Hey if you could mock one up that would be great.

Thanks

Dan


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Expert
posted Hide Post
I did some more thinking on this, will mock something up and post it as soon as I can. I have lots of meetings today which is unusual for me.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
Dan,

This is very simple but if you paste this into an adhoc window and do a View Source, you will see what the SET ASNAMES=ON buys you.

SET ASNAMES=ON
TABLE FILE CAR
SUM SALES
BY BODYTYPE
ACROSS COUNTRY
WHERE BODYTYPE EQ 'SEDAN'
WHERE COUNTRY NE 'FRANCE' OR 'W GERMANY'
ON TABLE HOLD FORMAT ALPHA
END
? HOLD HOLD
TABLE FILE HOLD
PRINT SALENGLAND
SALITALY
COMPUTE VAR1/F8.2=((SALITALY-SALENGLAND)/SALENGLAND)*100;
SALJAPAN
COMPUTE VAR2/F8.2=((SALJAPAN-SALITALY)/SALITALY)*100;
BY BODYTYPE
END

This paradigm loses its appeal if the number of ACROSS values varies. There are ways around that too but I won't go into that here. My goal is to get you going.

Another suggestion is to look at code in the ibinccen directory. There are examples of variance reports there.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders