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
October 08, 2007, 09:30 AM
GinnyJakes
Dan,
Please post the masters and a mockup of what you want the report to look like.
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
October 08, 2007, 10:55 AM
GinnyJakes
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
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
October 09, 2007, 04:17 PM
GinnyJakes
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.
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
October 10, 2007, 04:18 PM
GinnyJakes
Open a New Feature Request (NFR) with IBI CSS. Sounds like a good idea.
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
DanThis message has been edited. Last edited by: moyer1dl,
IBM Main Frame: MVS, FIX, VSAM
Windows SQL
WF 7.7
October 10, 2007, 05:14 PM
GinnyJakes
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.
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.