Hi all,

Hopefully this is an easy request. I have done some searching and found other posts relating to Column totals, but not Row totals with Across.

I have a table that list 3 columns:

MINS - Number of minutes
C_HOURS - Computed field, performing an IMOD by 60 on MINS to retrieve the number of whole hours
C_MINS - Computed field, performing an INT on the remainder.

Here is my table and results I am seeing:

TABLE FILE TEST
SUM
C_DIFF AS 'MINS'
COMPUTE C_HOURS/I5=INT(C_DIFF/60);
COMPUTE C_MINS/I5=IMOD(C_DIFF,60,'I5');

BY
ID AS ' '
ACROSS
DAY_NO AS ' '
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END
-RUN



So for the first day (number 3), the total minutes were 570m. C_HOURS and C_MINS are correct at 9hr 30m.
For the second day (number 4), the total minutes were 630m. C_HOURS and C_MINS are correct at 10hr 30m. etc ect

However when we come to the ROW TOTAL the 3 columns are totalled after the Computes have taken place. This gives me the incorrect result for 1830 minutes of 29hr 90m. I need it to read 30hr 30m.

I found the following document, which suggests to use COMPUTES and SUMMARIZE but I cannot get this to work with ROW-TOTALS and ACROSS.

Cheers

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

Mark,

I believe ON TABLE RECOMPUTE should work.

Sean

Hi Sean,

I have tried using ON TABLE RECOMPUTE, but it doesn't make any difference?

You might want to research the MacGyver technique.MacGyver

Mark,

Try this -

APP FI MARK66 DISK MARK66.MAS (LRECL 80
-RUN
-WRITE MARK66
-WRITE MARK66 FILE=MARK66,SUFFIX=FOC
-WRITE MARK66 SEGNAME=SEG1
-WRITE MARK66 FIELD=ITEM,  ,I4   ,I4   , $
-WRITE MARK66 FIELD=MINS,  ,I4   ,I4   , $
-RUN
CREATE FILE MARK66
MODIFY FILE MARK66
FIXFORM ITEM/A4 MINS/A4
DATA
00030570
00040630
00050330
00060300
END
-RUN
TABLE FILE MARK66
SUM MINS
    COMPUTE C_HOURS/I5=INT(MINS/60);
    COMPUTE C_MINS/I5=IMOD(MINS,60,'I5');
ACROSS ITEM RECOMPUTE
END
-RUN

The first piece of code is to replicate your data values.

T

Mark,

I see what you mean.

I have recreated your dilemma using the CAR file, so hopefully others can take a crack at it too:

  
TABLE FILE CAR
SUM
COMPUTE XYZ/I5 = WHEELBASE;
COMPUTE C_HOURS/I5=INT(XYZ/10);
COMPUTE C_MINS/I5=IMOD(XYZ,10,'I5');
ACROSS SEATS
BY COUNTRY
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END

The total values for W Germany are not showing up properly. C_HOURS is showing as 71, but should be 72, and C_MINS is showing as 11, and should be 1.

I'll let you know if I figure it out.

Sean

Mark,

I modified my code sample to work in 7.6.2, but it does not work in 5.2.8. So I don't know if this will work for you in 5.3.5 or not:

TABLE FILE CAR
SUM
COMPUTE XYZ/I5 = WHEELBASE;
COMPUTE C_HOURS/I5=INT(XYZ/10);
COMPUTE C_MINS/I5=IMOD(XYZ,10,'I5');
ACROSS SEATS AND RECOMPUTE AS 'Row Totals'
BY COUNTRY
ON TABLE NOTOTAL
END

Sean

v5.3.4 gives me an error for both Tony's and Sean's examples:

(FOC002) A WORD IS NOT RECOGNIZED: RECOMPUTE

I guess new functionality was added for ACROSS in v7.

Depends upon which release Sean tested his version - 5.2.8 (Prod) or 7.6.2 (Test)?

Just goes to show how important having the detail in the signature is!

T

Thanks guys, but unfortunately nothing working yet. I also get (FOC002) NOT RECOGNIZED: RECOMPUTE.

I guess its new to v7.

Mark,

You could try the following code (which works for every release as far as I know):

TABLE FILE TEST
SUM C_DIFF AS 'MINS'
COMPUTE C_HOURS/I5=INT(C_DIFF/60);
COMPUTE C_MINS/I5=IMOD(C_DIFF,60,'I5');
BY ID AS ' '
ACROSS DAY_NO AS ' '
COMPUTE TMIN/I5 = C1 + C4 + C7 + C10 + C13 + C16; AS 'TOTAL'
COMPUTE CHRS/I5 = INT(TMIN/60); AS ''
COMPUTE CMIN/I5 = IMOD(TMIN,60,'I5'); AS ''
ON TABLE NOTOTAL
END

Only there are two things to be aware of with this approach:
1. titles of the computes. Can't seem to find a way around that.
2. you never knoe up front how many across values there will be (hence the c1+c4+c7+ etc.). Maybe you can make this dynamic.

Hope this helps.

Mark,
Try the following:

DEFINE FILE CAR
W0/I6=WHEELBASE;
W1/I6=WHEELBASE;
END
TABLE FILE CAR
SUM W0 NOPRINT
BY COUNTRY
SUM
COMPUTE C_DIFF/I5 = W1;
COMPUTE C_HOURS/I5=INT(C_DIFF/60);
COMPUTE C_MINS/I5=IMOD(C_DIFF,60,'I5');
BY COUNTRY
ACROSS SEATS 
COMPUTE T_DIFF/I6=W0;
T_HOURS/I5=INT(T_DIFF/60);
T_MINS/I5=IMOD(T_DIFF,60,'I5');
ON TABLE NOTOTAL
END

With this coding you do not have any problems with the number of across ACROSS values.

Hi all,

Thanks again for everyones help and time spent looking at this for me, I really appreciate it!

GamP, as the number of across values is always changing, I really wasn't sure if I could use this approach, eg to make the code clever enough to work out the calculation depending on the number of columns.

Danny-SRL, unfortunately I could not get your solution to work either. It just gave me the total time for the first day?

For the time being (until we go to v7) I have found a relatively simple workaround. I first build a basic hold file, summarising the total time worked in the desired format by user. I then join to this file by user ID and just use the total time as a 3rd BY field:



The total time logged isn't on the right hand side as you would expect it to be, but at least the information is there!

If I could ask one last question....

How can I suppress drill down links if there is no data for that cell? Currently I have a '.' with a hyper link in. I would prefer if I had nothing.

Cheers

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

Mark,
This is what I get when I run the fex I sent:

  
PAGE     1
  
  
              SEATS 
                2                      4                      5                     T_DIFF T_HOURS T_MINS
  COUNTRY     C_DIFF C_HOURS C_MINS  C_DIFF C_HOURS C_MINS  C_DIFF C_HOURS C_MINS                        
  -------------------------------------------------------------------------------------------------------
  ENGLAND        190       3     10     105       1     45     112       1     52      407       6     47
  FRANCE           .       .      .       .       .      .     108       1     48      108       1     48
  ITALY          282       4     42     101       1     41       .       .      .      383       6     23
  JAPAN            .       .      .     185       3      5       .       .      .      185       3      5
  W GERMANY        .       .      .      98       1     38     621      10     21      719      11     59

Hi Danny,

Ok, I have managed to get your code to work with my example!



However, I am struggling to understand exactly how it is working. The code looks different to anything I have worked with in my limited experience, for example:

Having two SUM statements
Multiple COMPUTE statements (T_DIFF, T_HOUR, T_MINS) yet with only one COMPUTE coded?

I also find that I am unable to hide (NOPRINT) the T_DIFF column. My only option is to delete it? Ideally I need to reformat T_HOUR and T_MINS together to look like a proper time format HH:MM.

Thanks again for your help, ideally I would much prefer to use your method over my hold file workaround, but only if I can achieve the one total column in HH:MM.

Cheers

Mark,

Just perform your required single output in one go -

COMPUTE TOT_LOGGED/A8 = FTOA(INT(MINS/60), '(F5)', 'A5')||':'||FTOA(IMOD(MINS,60,'F2L'), '(F2L)', 'A2');

T

Thanks Tony - Nearly there!,

I used your one liner and converted it to:

COMPUTE TOT_LOGGED/A8 = FTOA(INT(W0/60), '(F5)', 'A5')||':'||FTOA(IMOD(W0,60,'F2L'), '(F2L)', 'A2');
H

It looks like it is doing the trick, but it is having a problem with the minutes:



I feel embarrased that you guys have got me so close and yet I can't fix this last bit myself

What is the F2L format?

Mark,
To answer your questions:
The 2 SUM statements are called "multi-verb requests". This has always been one of the great fortes of Focus and Webfocus. You can find the documentation in the manual (see page 4-37 in the 7.6 creating reports manual).
Concerning COMPUTE statements in a TABLE command, you can have any number of statements, one after the other, after a single COMPUTE. However if after a ';' you write NOPRINT then for the next computed statement you must code COMPUTE again.
To display your hours in the HH:MM form you can use Tony's example.
Regards

Mark,

The F2L is F2 with leading zeroes. It ensures that you do not get 00: 0 output!! The reason that you are getting 30:** is probably due to the format you are giving to W0 - change it to F5 for my example.

Using the code that I used above, but with Danny's set-up, try this -

APP FI MARK66 DISK MARK66.MAS (LRECL 80
-RUN
-WRITE MARK66
-WRITE MARK66 FILE=MARK66,SUFFIX=FOC
-WRITE MARK66 SEGNAME=SEG1
-WRITE MARK66 FIELD=ID,          ,A4   ,A4   ,FIELDTYPE=I, $
-WRITE MARK66 FIELD=LOG_DATE,    ,DMYY ,DMYY , $
-WRITE MARK66 FIELD=MINS,        ,I4   ,I4   , $
-RUN
 
CREATE FILE MARK66
MODIFY FILE MARK66
FIXFORM ID/A4 LOG_DATE/A8 MINS/A4
DATA
ERV 031020070570
ERV 041020070630
ERV 051020070330
ERV 061020070300
FVM 031020070570
FVM 041020070930
FVM 051020070430
FVM 061020070400
ZPM 031020070770
ZPM 041020070730
ZPM 061020070700
END
-RUN
DEFINE FILE MARK66
  MINS0/F5 = MINS;
  MINS1/F5 = MINS;
END
TABLE FILE MARK66
SUM MINS0 NOPRINT
BY ID AS ''
SUM COMPUTE SUM_LOGGED/A8 = FTOA(INT(MINS1/60), '(F5)', 'A5')||':'||FTOA(IMOD(MINS1,60,'F2L'), '(F2L)', 'A2'); AS ''
BY ID AS ''
ACROSS LOG_DATE AS ''
-*       RECOMPUTE AS 'Total'
    COMPUTE TOT_LOGGED/A8 = FTOA(INT(MINS0/60), '(F5)', 'A5')||':'||FTOA(IMOD(MINS0,60,'F2L'), '(F2L)', 'A2'); AS 'Total'
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN

The reason that Danny had three fields and only one compute is that the compute is inferred in subsequent calcs. Not what I would recommend because it does make the code a little difficult to read by inexperienced WF coders - as you found yourself. And from a maintenance point of view that becomes important.

Note that the COMPUTE that Danny uses after the final verb (ACROSS) isn't preceded by a SUM or PRINT etc. and this actually performs the same as a RECOMPUTE would in 7.n (almost )

Good luck

T

Danny, thanks for your explanation if the Multi_verb, I will look it up

Tony,

I have tried all combinations of formats, but I am still getting the ** in the minutes:



The only difference in my code and yours is MINS in the DEFINE. I am actually calculating MINS from the number of minutes in the End Time of each log minus the number of minutes in the Start Time of each log.

DEFINE FILE LOG
C_START_MINS/I5=INT(TBTIME/100) * 60 + IMOD(TBTIME,100,'I5');
C_END_MINS/I5=INT(TETIME/100) * 60 + IMOD(TETIME,100,'I5');
-*C_DIFF/I5=C_END_MINS-C_START_MINS
MINS0/F5 = C_END_MINS-C_START_MINS;
MINS1/F5 = C_END_MINS-C_START_MINS;
END

I have been using START_MINS and END_MINS to calculate MINS, based on the following data:



(I will probably find out that there is a much easier way of working with these times?!)

Can you see spot why I am getting the **?

Mark,

Without your full code and data it would be difficult to discern why you are getting the **.

However, using the same data as you in (yet again) the example I used above, but this time using a start and end time in HHMM format (as integer e.g. 08:00 am = 800 etc.), try this -

APP FI MARK66 DISK MARK66.MAS (LRECL 80
-RUN
-WRITE MARK66
-WRITE MARK66 FILE=MARK66,SUFFIX=FOC
-WRITE MARK66 SEGNAME=SEG1, SEGTYPE=S2, $
-WRITE MARK66 FIELD=ID,          ,A4   ,A4   ,FIELDTYPE=I, $
-WRITE MARK66 FIELD=LOG_DATE,    ,I4   ,I4   ,FIELDTYPE=I, $
-WRITE MARK66 FIELD=TBTIME,      ,I4   ,I4   , $
-WRITE MARK66 FIELD=TETIME,      ,I4   ,I4   , $
-RUN
 
CREATE FILE MARK66
MODIFY FILE MARK66
FIXFORM ID/A4 LOG_DATE/A4 TBTIME/A4 TETIME/A4
DATA
ERV    3 800 900
ERV    3 9001300
ERV    313001730
ERV    4 7001730
ERV    5 7001230
ERV    6 8301330
END
-RUN
DEFINE FILE MARK66
  SMINS/F5=INT(TBTIME/100) * 60 + IMOD(TBTIME,100,'I5');
  EMINS/F5=INT(TETIME/100) * 60 + IMOD(TETIME,100,'I5');
  MINS1/F5 = EMINS - SMINS;
END
TABLE FILE MARK66
SUM COMPUTE MINS0/F5 = EMINS - SMINS; NOPRINT
BY ID AS ''
SUM COMPUTE SUM_LOGGED/A8 = FTOA(INT(MINS1/60), '(F5)', 'A5')||':'||FTOA(FMOD(MINS1,60,'F2L'), '(F2L)', 'A2'); AS ''
BY ID AS ''
ACROSS LOG_DATE AS ''
-*       RECOMPUTE AS 'Total'
    COMPUTE TOT_LOGGED/A8 = FTOA(INT(MINS0/60), '(F5)', 'A5')||':'||FTOA(FMOD(MINS0,60,'F2L'), '(F2L)', 'A2'); AS 'Total'
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN

Note the change from IMOD to FMOD.

Gives this -

	3	4	5	6	Total
ERV	9:30	10:30	5:30	5:00	30:30

T

Excellent stuff Tony, the change from IMOD to FMOD fixed it!



Again, a very big Thank You to all of you for your excellent help!!