[SOLVED] CASE DIFFICULT TO EXPLAIN

July 09, 2015, 07:07 AM

Cati - France

[SOLVED] CASE DIFFICULT TO EXPLAIN - SEE JOIN EXAMPLE

Hello Webfocus Users,
I have a problem witch is difficult to explain for me, so I prepared an example with Employee table.

If you execute the following lines, you obtain 2 data lines for field "number of months" equal to 401 and a totalisation line *TOTAL 401.

the value of field amount by month is : 21780 + 9000 = 30 780 / (401 + 401) = 38,38

The number of months is summed and I don't want. I want 30780/401 = 76,76 as result on the total line.

How can I do that ? (if the field number_of_months is in first position in the BY list,it works but in my case, the field can not be in first position).
Thanks a lot for answers.
Catherine

-SET &END_DATE = '09072015';

DEFINE FILE EMPLOYEE
NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
NEW_DAT_INC/YYMD = DATECVT(&END_DATE, 'I8MDYY', 'YYMD');
NUMBER_OF_MONTHS/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
END

TABLE FILE EMPLOYEE
SUM
'EMPLOYEE.EMPINFO.NEW_HIRE_DATE'
'EMPLOYEE.EMPINFO.CURR_SAL/D12.2!e'
COMPUTE amount_by_month/D12.2 = EMPLOYEE.EMPINFO.CURR_SAL / EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS;
BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
BY 'EMPLOYEE.EMPINFO.DEPARTMENT'
BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
BY 'EMPLOYEE.EMPINFO.LAST_NAME'
BY 'EMPLOYEE.EMPINFO.FIRST_NAME'
WHERE DEPARTMENT EQ 'MIS';
ON EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS SUBTOTAL RECOMPUTE AS '*TOTAL'
ON TABLE NOTOTAL
END

This message has been edited. Last edited by: <Kathryn Henning>,

July 09, 2015, 07:52 AM

MartinY

Just remove the RECOMPUTE from the SUBTOTAL line

-SET &END_DATE = '09072015';

DEFINE FILE EMPLOYEE
NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
NEW_DAT_INC/YYMD = DATECVT(&END_DATE, 'I8MDYY', 'YYMD');
NUMBER_OF_MONTHS/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
END

TABLE FILE EMPLOYEE
SUM
 'EMPLOYEE.EMPINFO.NEW_HIRE_DATE'
 'EMPLOYEE.EMPINFO.CURR_SAL/D12.2!e'
 COMPUTE amount_by_month/D12.2 = EMPLOYEE.EMPINFO.CURR_SAL / EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS;
-*BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
BY 'EMPLOYEE.EMPINFO.DEPARTMENT'
BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
BY 'EMPLOYEE.EMPINFO.LAST_NAME'
BY 'EMPLOYEE.EMPINFO.FIRST_NAME'
WHERE DEPARTMENT EQ 'MIS'; 
ON EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS SUBTOTAL  AS '*TOTAL'
ON TABLE NOTOTAL
END

Is that what you're looking for ?

July 09, 2015, 08:00 AM

Cati - France

Tkanks for the answer.
But I need to have RECOMPUTE because in my real case, I have other computed field for percentage calculation.

July 09, 2015, 08:58 AM

MartinY

Does this suits more your need ?

-SET &END_DATE = '09072015';

DEFINE FILE EMPLOYEE
NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
NEW_DAT_INC/YYMD = DATECVT(&END_DATE, 'I8MDYY', 'YYMD');
NUMBER_OF_MONTHS/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
END

TABLE FILE EMPLOYEE
-*SUM 'EMPLOYEE.EMPINFO.CURR_SAL/D12.2!e' NOPRINT
-*    COMPUTE amount_by_month/D12.2 = EMPLOYEE.EMPINFO.CURR_SAL / FST.EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS; NOPRINT
BY 'EMPLOYEE.EMPINFO.DEPARTMENT'
BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
WHERE DEPARTMENT EQ 'MIS'; 

PRINT 'EMPLOYEE.EMPINFO.NEW_HIRE_DATE'
      'EMPLOYEE.EMPINFO.CURR_SAL/D12.2!e'
      COMPUTE amount_by_usermonth/D12.2 = EMPLOYEE.EMPINFO.CURR_SAL / EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS;
BY 'EMPLOYEE.EMPINFO.DEPARTMENT'
BY 'EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS'
BY 'EMPLOYEE.EMPINFO.LAST_NAME'
BY 'EMPLOYEE.EMPINFO.FIRST_NAME'
WHERE DEPARTMENT EQ 'MIS'; 
ON EMPLOYEE.EMPINFO.NUMBER_OF_MONTHS SUBTOTAL RECOMPUTE AS '*TOTAL'
ON TABLE NOTOTAL
END

July 09, 2015, 12:32 PM

Mighty Max

  
-SET &END_DATE = '09072015';

DEFINE FILE EMPLOYEE
NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD');
NEW_DAT_INC/YYMD = DATECVT(&END_DATE, 'I8MDYY', 'YYMD');
NUMBER_OF_MONTHS/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'M');
END

TABLE FILE EMPLOYEE
SUM
   NEW_HIRE_DATE
   CURR_SAL/D12.2!e
   COMPUTE cnt/I11 = CNT.NUMBER_OF_MONTHS; 
   COMPUTE amount_by_month/D12.2 = CURR_SAL / (NUMBER_OF_MONTHS/cnt);
BY NUMBER_OF_MONTHS
BY DEPARTMENT
BY NUMBER_OF_MONTHS
BY LAST_NAME
BY FIRST_NAME
WHERE DEPARTMENT EQ 'MIS';
ON NUMBER_OF_MONTHS SUBTOTAL RECOMPUTE AS '*TOTAL'
ON TABLE NOTOTAL
END