 |
|||||
|
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
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 TopicGo | Search | Notify | Tools |
| Variance Analysis | Login/Join |
| Platinum Member |
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 | ||
|
| Expert |
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 | |||
|
| Platinum Member |
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 DanThis message has been edited. Last edited by: moyer1dl, IBM Main Frame: MVS, FIX, VSAM Windows SQL WF 7.7 | |||
|
| Expert |
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 | |||
|
| Platinum Member |
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.1This message has been edited. Last edited by: moyer1dl, IBM Main Frame: MVS, FIX, VSAM Windows SQL WF 7.7 | |||
|
| Expert |
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 | |||
|
| Platinum Member |
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 | |||
|
| Expert |
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 | |||
|
| Platinum Member |
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 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 DanThis message has been edited. Last edited by: moyer1dl, IBM Main Frame: MVS, FIX, VSAM Windows SQL WF 7.7 | |||
|
| Expert |
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 | |||
|
| Platinum Member |
Hey if you could mock one up that would be great. Thanks Dan IBM Main Frame: MVS, FIX, VSAM Windows SQL WF 7.7 | |||
|
| Expert |
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 | |||
|
| Expert |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |