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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sum a field and then subtract it from another field
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Sum a field and then subtract it from another field
 Login/Join
 
Member
posted
Good morning,
I want to calculate up the total credit hours a students withdrew from in their courses for the semester. For instance, a person is enrolled in 4 courses for a total of 12 total credit hours. Each course is 3 hours. The student withdrew from 2 courses, which is a total of 6 withdrawn hours. He has 6 remaining hours.

I have the total credit hours which is 12. The issue is adding each course that is withdrawn and then subtracting them from the total credit hours.

The program is below. Please assist.

Looking at ENROLLED HRS and SECTION HRS.
(Summing the section hours)



-* FF Line do not change this line! Segment
JOIN
AS_STUDENT_REGISTRATION_DETAIL.PIDM_KEY
IN AS_STUDENT_REGISTRATION_DETAIL
TO SFBETRM.SFBETRM_PIDM
IN SFBETRM
AS J001
END

DEFINE FILE AS_STUDENT_REGISTRATION_DETAIL
SEM/A15 = IF EDIT('&TERM', '$$$$99') EQ '60' THEN 'Main Fall' ELSE
IF EDIT('&TERM', '$$$$99') EQ '20' THEN 'Main Spring' ELSE
IF EDIT('&TERM', '$$$$99') EQ '30' THEN 'Main Summer I' ELSE
IF EDIT('&TERM', '$$$$99') EQ '40' THEN 'Main Summer II' ELSE
IF EDIT('&TERM', '$$$$99') EQ '65' THEN 'FT SAM Fall/Win' ELSE
IF EDIT('&TERM', '$$$$99') EQ '25' THEN 'FT SAM Win/Spr' ELSE
IF EDIT('&TERM', '$$$$99') EQ '35' THEN 'FT SAM Spr/Sum' ELSE
IF EDIT('&TERM', '$$$$99') EQ '45' THEN 'FT SAM Sum/Fall' ELSE ' ';
YR1/A4 = EDIT('&TERM', '9999');
SEM2/A15 = IF EDIT('&TERM1', '$$$$99') EQ '60' THEN 'Main Fall' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '20' THEN 'Main Spring' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '30' THEN 'Main Summer I' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '40' THEN 'Main Summer II' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '65' THEN 'FT SAM Fall/Win' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '25' THEN 'FT SAM Win/Spr' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '35' THEN 'FT SAM Spr/Sum' ELSE
IF EDIT('&TERM1', '$$$$99') EQ '45' THEN 'FT SAM Sum/Fall' ELSE ' ';
YR2/A4 = EDIT('&TERM1', '9999');
BIRTH_DT/YYMD = HDATE(BIRTH_DATE, 'YYMD');
BEGIN_DATE/YYMD = '&BEGDATE. Today's Date: Format YYYYDDMM.';
STATUS_DATE/YYMD = HDATE(REGISTRATION_STATUS_DATE, 'YYMD');
AGE_IN_DAYS/I5 = BEGIN_DATE - BIRTH_DT;
AGE/I2 = AGE_IN_DAYS/365.25;
SECTION_HRS/D12.2 = SECTION_CREDIT_HOURS;
ENROLLED_HRS/D12.2 = TOTAL_CREDIT_HOURS - SECTION_HRS;
NAME/A100 = LAST_NAME || ( ', ' | FIRST_NAME || ( ' ' | MIDDLE_INITIAL ) ) ;
NAME1/A20 = EDIT ( NAME , '99999999999999999999' ) ;
FLAG/A1 = IF ID EQ LAST ID THEN 'N' ELSE 'Y';
COURSE/A12 = SUBJ_CODE || COURSE_NUMBER || SECTION_NUMBER ;
COURSE1/A11 = IF SUBJ_CODE = 'CSC' THEN EDIT (COURSE , '999-999-99') ELSE
IF SUBJ_CODE = 'MIS' THEN EDIT (COURSE , '999-999-99') ELSE
IF SUBJ_CODE = 'HCM' THEN EDIT (COURSE , '999-999-99') ELSE
IF SUBJ_CODE = 'ART' THEN EDIT (COURSE , '999-999-99') ELSE EDIT( COURSE , '9999-999-99' ) ;
ADVISOR/A77 = ADVISOR_LAST_NAME1 || (', ' | ADVISOR_FIRST_NAME1);
INSTRUCTOR/A77 = INSTRUCTOR_LAST_NAME || (', ' | INSTRUCTOR_FIRST_NAME);
INSTNAME/A20 = EDIT(INSTRUCTOR, '99999999999999999999');
ADVNAME/A20 = EDIT(ADVISOR, '99999999999999999999');
END

TABLE FILE AS_STUDENT_REGISTRATION_DETAIL
HEADING CENTER
"FAYETTEVILLE STATE UNIVERSITY"
"STUDENTS WHO HAVE WITHDRAWN FROM A COURSE"
-*""" "
SUM SECTION_HRS
BY NAME1 AS 'STUDENT NAME'
PRINT
ID AS 'BANNER'
MAJR_DESC1 AS 'MAJOR'
AGE
CRN_KEY AS 'CRN'
COURSE1 AS 'COURSE'
SECTION_HRS AS 'WITHDRAWN HRS.'
-*TOTAL_CREDIT_HOURS
INSTNAME AS 'INSTRUCTOR'
GRDE_CODE AS 'GRADE'
STATUS_DATE AS 'WITHDRAWAL DATE'
ENROLLED_HRS AS 'CURRENT ENROLLED HRS.'
BY NAME1 AS 'STUDENT NAME'
SKIP-LINE
WHERE REGISTERED_IND EQ 'Y'
AND (TERM_CODE_KEY EQ '&TERM' OR '&TERM1')
-*AND TERM_CODE_KEY EQ '&TERM. Term.'
AND GRDE_CODE EQ 'W'
AND EMAIL_TYPE EQ 'C' OR 'P'
ON TABLE PCHOLD FORMAT EXL2K
END

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 28 | Registered: January 10, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Try setting a defined field with withdrawal hrs and just add the two fields:
withdraw/d12.2 = if withdraw then -1 * enrolled_hrs else 0;

Something like that --

Hope it helps.

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


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Reply With QuoteReport This Post
Member
posted Hide Post
Vivian,
I read your post but need clarification. I have added the defined withdraw field which I believe is adding up the number of withdraw courses, but I need to count the credit hours of the withdrawn courses and subtract it from the total credit hours (which is the total number of hours enrolled at the beginning of the semester). The statement you provided seems to only count the number of courses that the student may have withdrawn from. Please clarify.


WebFOCUS 8
Windows, All Outputs
 
Posts: 28 | Registered: January 10, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
Terri1977,

If you run a report and SUM the new defined field BY NAME1 along with your other fields do you see the correct results in the new defined field? I think you say it gives the right results. Run a basic report to see your results then add in your calculation.

For - "need to count the credit hours of the withdrawn courses" - Set up another defined field for this calculation.

You have a multi verb request and therefore are SUMming fields and PRINTing fields. You can also use column notation to set up a COMPUTE in the PRINT section to do the subtraction.

As you mentioned - need to count the credit hours of the withdrawn courses and subtract it from the total credit hours.
- Total credit hours can be done in your SUM section
- create a compute using column notation to do the subtraction

There are many examples of using column notation on the forum: I searched on "column notation"

[URL=http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3691084132?r=7601005132#7601005132 ]Calculating percentages off a subtotal line[/URL]
[Solved] How to get the earliest/latest datetime within sort group, with Prefix?

Update us on your progress.

Thank you for participating in the Focal Point Forum!
Tamra Colangelo
Focal Point Moderator
Information Builders


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sum a field and then subtract it from another field

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