I needa field format that will take a number of minutes, eg, 810, and express it as a time of day, eg 13:30. something like TOD/HM= 810 ; tableau has it. bo has it. do we??This message has been edited. Last edited by: <Emily McAllister>,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 24, 2016, 10:06 PM
Waz
I think you need to use HADD with a base date set to Zero Hours.
Internally I'm not sure that format DateTime fields are, so, don't like your chances on just assigning it.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 24, 2016, 10:09 PM
Waz
You could set up a define function to do the change, so its simple there then on.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 25, 2016, 04:44 AM
Wep5622
HADD sort of works, unless you have durations that span more than 24 hours.
When that happens, hours wrap back to 0 and you also need to print the number of days. Unfortunately, when using datetime format to print a duration you're dealing with dates. Dates always start at the 1st of a month, so for 810 minutes you end up with "1 13:30" instead of "0 13:30". For 2250 minutes you end up with "2 13:30" instead of "1 13:30", etc.
And even if you fix that, the result is still a bit cryptic to read for end users...
I suppose that's why some RDBMSes have an interval type for the difference between two date-times (then again, not many database connection libraries can deal with such types properly anyway).
In the end, we ended up converting the duration to an alpha field with the number of hours concatenated with the remaining number of minutes, resulting in "13:30" and "37:30" for the respective example durations.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
August 25, 2016, 09:33 AM
susannah
thanks folks. alas, alpha is what I needed to avoid. sigh...
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 25, 2016, 10:19 AM
Tony A
Of course there is -
TABLE FILE GGSALES
SUM COMPUTE MYHDATE/HYYMDS = HADD(HINPUT(8, DATECVT(DATE, 'I8YYMD', 'A8YYMD'), 8, 'HYYMDS'), 'MINUTES', 810, 8, 'HYYMDS');
COMPUTE MYHTIME/HHI = HHMS(MYHDATE, 8, 'HHI');
BY DATE
END
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 25, 2016, 10:43 AM
eric.woerle
As Tony shows, yes WF has a format for time of day. It does not however have a function for Time Elapsed. I remember looking for that a few months ago and not really seeing anyone that has that.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
August 25, 2016, 02:49 PM
David Briars
quote:
As Tony shows, yes WF has a format for time of day. It does not however have a function for Time Elapsed.
If I end up needing to do a tablature report with Time Elapsed, I'll definitely use that post. It'll save me the time of trying to figure it out myself. Unfortunately That doesn't work for graphing, which is what I needed it for. Maybe I can work some trickery to make mask the Y axis or something. But I haven't really been able to find a good way to graph elapsed time. This is where a data type would come in handy.... But I don't have 10 years to wait for some one to find time to read such an NFR... so not going to waste my time asking for it.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
August 25, 2016, 06:14 PM
Waz
At the time I posted this its was working. (WF 7.6)
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 26, 2016, 10:16 AM
eric.woerle
Tony,
You are absolutely correct. In what I was working on though, I was creating graphs, not tablature reports. So I guess, more accurately... I would have needed a date format like Susannah is talking about in this thread. You can't graph Alpha values is the problem. They are all just counts. But I'll revisit this in 10 years when IBI decides to consider that NFR Doug put in. Only 9 more years till we get it.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
August 26, 2016, 11:03 AM
Tony A
quote:
But I'll revisit this in 10 years
I shall be retired by then ... but probably still visiting the forum
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 28, 2016, 05:59 PM
Waz
quote:
I shall be retired by then
That will be a great loss the WebFOCUS world.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 29, 2016, 08:53 AM
Tony A
Thanks for the sentiment Waz There are many members of the forum to whom that also applies!
It is good that there are a few younger members that are becoming revered in their own right. I have recently had the pleasure of meeting a few of them and the future appears to be assured! My best wishes to each and every one of them!
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 29, 2016, 06:03 PM
Waz
You're bringing a tear to my eye.
No, really you are.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 30, 2016, 02:45 AM
Tony A
Not just the Aussie wind then
T
August 30, 2016, 03:41 PM
susannah
T...that might work.... stay tuned...
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 31, 2016, 10:55 AM
susannah
ah rats, I can't control the y axis enough.. but I might well use it for the display table. its cooler than minutes/60 : the imod
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
August 31, 2016, 10:59 AM
Tony A
quote:
I can't control the y axis enough..
Example?
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 31, 2016, 10:41 PM
David Briars
This has the making of another great FP thread...interesting business need, excellent ideas, and a dash of humor. :-)
I think a consensus of the requirements are something like: * incoming date-time fields * need visualization with y-axis representing duration in hours:minutes.
Here is something I've been working on, as I think I might have this kind of requirement in the near future.
-* File yaxistime.fex
-*
-* Extract data from database.
-*
DEFINE FILE GGSALES
-* Not part of the technique; create data for testing.
NAME/A20 = DECODE SEQ_NO (1 'Francis' 2 'Tony' 3 'David' 4 'Waz');
DATE_START/HYYMDS = DT(2005/12/26 05:45:05.000);
DATE_END/HYYMDS = IF SEQ_NO EQ 1 THEN DT(2005/12/26 08:46:06.000) ELSE
IF SEQ_NO EQ 2 THEN DT(2005/12/26 07:47:06.000) ELSE
IF SEQ_NO EQ 3 THEN DT(2005/12/26 07:47:06.000) ELSE
DT(2005/12/26 16:00:00.000) ;
END
TABLE FILE GGSALES
PRINT SEQ
NAME
COMPUTE MN_DIFF/I9 = HDIFF(DATE_END,DATE_START,'MINUTE','I9');
-*
IF RECORDLIMIT EQ 4
ON TABLE HOLD AS HLDTIME
END
-RUN
-*
-* Create visualization.
-*
GRAPH FILE HLDTIME
SUM MN_DIFF AS 'Time Spent Studying'
BY NAME AS 'Student'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_JS
title: {text: 'Study Hall Report', visible: true},
subtitle: {text: 'Hours:Minutes Spent Studying Per Student', visible: true}
*END
*GRAPH_JS
yaxis: {
numberFormat: function(n){ var hours = Math.floor(Math.abs(n) / 60);
var minutes = Math.abs(n) % 60;
minutes = (minutes < 10) ? '0' + minutes : minutes
return hours + ':' + minutes;
},
intervalMode: 'interval',
intervalValue: 60
}
*END
*GRAPH_JS
series: [
{series: 'reset', tooltip: function(v, s, g) { var hours = Math.floor(Math.abs(v) / 60);
var minutes = Math.abs(v) % 60;
minutes = (minutes < 10) ? '0' + minutes : minutes
var time = hours + ':' + minutes;
return 'Name: ' + this.getGroupLabel(g) +
'<br/>Time: ' + time;}}
]
*END
ENDSTYLE
END
Basically, the process is to draw the bars using minutes, then reformat the axis labels to 'HH:MM'.
All suggestions welcome, as always. (For example, I'd probably want to put the JS calculations in functions, that could be called.)
August 31, 2016, 10:51 PM
Waz
Time to stop studying and get back to work...
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
August 31, 2016, 10:56 PM
David Briars
quote:
Time to stop studying and get back to work..
September 01, 2016, 03:18 AM
Tony A
David,
Nice method and very similar idea to something I have tried to do on date reformatting for Gantt charts (needs more work ).
Unlike Waz, I guess that I need to study a little more!
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
September 01, 2016, 04:16 AM
Wep5622
Solid idea David; it will sort correctly due to the value being in minutes and it can handle values exceeding 24 hours.
I get the feeling that it should be possible to turn this into SVG format somehow so that we can also use this in, say, PDF output...
I know from experience that web browsers can make that conversion, but doing that server-side could be a challenge.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
September 02, 2016, 10:56 AM
eric.woerle
David,
I think I owe you a beer! One of these days I'm going to have to meet you at the bar after work! I spent a lot of time trying to figure out how to get this to work!
-* File yaxistime.fex
-*
-* Extract data from database.
-*
DEFINE FILE GGSALES
-* Not part of the technique; create data for testing.
NAME/A20 = DECODE SEQ_NO (1 'Francis' 2 'Tony' 3 'David' 4 'Waz');
DATE_START/HYYMDS = DT(2005/12/26 05:45:05.000);
DATE_END/HYYMDS = IF SEQ_NO EQ 1 THEN DT(2005/12/26 08:46:06.000) ELSE
IF SEQ_NO EQ 2 THEN DT(2005/12/26 07:47:06.000) ELSE
IF SEQ_NO EQ 3 THEN DT(2005/12/27 07:47:06.000) ELSE
DT(2005/12/26 16:00:00.000) ;
END
TABLE FILE GGSALES
PRINT SEQ
NAME
COMPUTE MN_DIFF/I9 = HDIFF(DATE_END,DATE_START,'MINUTE','I9');
-*
IF RECORDLIMIT EQ 4
ON TABLE HOLD AS HLDTIME
END
-RUN
-*
-* Create visualization.
-*
GRAPH FILE HLDTIME
SUM MN_DIFF AS 'Time Spent Studying'
BY NAME AS 'Student'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_JS
title: {text: 'Study Hall Report', visible: true},
subtitle: {text: 'Hours:Minutes Spent Studying Per Student', visible: true}
*END
*GRAPH_JS
yaxis: {
numberFormat: function(n){ var days = Math.floor(Math.abs(n) / 60 / 24);
var hours = Math.floor(Math.abs(n) / 60);
var minutes = Math.abs(n) % 60;
hours = ( hours > 24 ) ? hours - (days * 24) : hours ;
days = ( days < 1 ) ? '' : days + 'd ' ;
minutes = (minutes < 10) ? '0' + minutes : minutes
return days + hours + ':' + minutes;
},
intervalMode: 'interval',
intervalValue: 60
}
*END
*GRAPH_JS
series: [
{series: 'reset', tooltip: function(v, s, g) { var days = Math.floor(Math.abs(v) / 60 / 24);
var hours = Math.floor(Math.abs(v) / 60);
var minutes = Math.abs(v) % 60;
hours = ( hours > 24 ) ? hours - (days * 24) : hours ;
days = ( days < 1 ) ? '' : days + 'd ' ;
minutes = (minutes < 10) ? '0' + minutes : minutes
var time = days + hours + ':' + minutes;
return 'Name: ' + this.getGroupLabel(g) +
'<br/>Time: ' + time;}}
]
*END
ENDSTYLE
END
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
September 03, 2016, 10:54 AM
David Briars
Thank you for the comments Waz, Tony, Wep, and Eric.
October 03, 2016, 10:39 AM
Tony A
So, being inspired by David's time reformatting of the yaxis using JS, I thought that, once I had a little time, I would revisit what I was doing for a Gantt chart.
FWIW, this is a revision of an example that I posted in December 2005 in the hope that it helps someone out.
As usual, the first section builds some sample data so that anyone can try this without having to have prepared data.
SET HOLDLIST = PRINTONLY
APP FI GANTT DISK GANTT.MAS (LRECL 80
-RUN
-WRITE GANTT
-WRITE GANTT FILE=GANTT,SUFFIX=FOC
-WRITE GANTT SEGNAME=SEG1
-WRITE GANTT FIELD=AREA,,A15,A15,$
-WRITE GANTT FIELD=TASK,,A27,A27,$
-WRITE GANTT FIELD=BEGDATE,,YYMD,YYMD,$
-WRITE GANTT FIELD=ENDDATE,,YYMD,YYMD,$
-RUN
CREATE FILE GANTT
MODIFY FILE GANTT
FIXFORM AREA/A15 TASK/A27 BEGDATE/A8 ENDDATE/A8
DATA
Engineering 1 Site Survey 2000101820001115
Engineering 2 Draft Survey Results 2000101820001204
Engineering 3 GMBH Review 2000120420001229
Engineering 4 Survey RPT (Final) 2001010120010122
Engineering 5 GMBH RFV Approval 2001010320010206
Engineering 6 System Design 2001021420010320
Engineering 7 HMBC Review 2001032020010404
Engineering 8 Bid and Award 2001040420010604
Engineering 9 Construction Support 2001042720010905
END
-RUN
DEFINE FILE GANTT
CURRDATE/YYMD = 20010113;
CURRTIME/HYYMDs = HDTTM(&YYMD, 8, 'HYYMDS');
END
TABLE FILE GANTT
SUM MAX.ENDDATE/I8
MIN.BEGDATE/I8
MAX.CURRDATE/I8
CURRTIME
ON TABLE SAVE FORMAT ALPHA
END
-RUN
-READ SAVE X4 &MaxDate.A8. X4 &MinDate.A8. X4 &CurrDate.A8. &CurrTime.A17.
-SET &MinDate = &MinDate - 14;
-SET &MaxDate = &MaxDate + 14;
-SET &CurrDate = &CurrDate - &MinDate + 28;
-RUN
DEFINE FILE GANTT ADD
OFFSET/I9 = 14;
DURATION/I9 = ENDDATE - BEGDATE;
REMAIN/I9 = IF ENDDATE LT CURRDATE THEN 0
ELSE IF CURRDATE FROM BEGDATE TO ENDDATE THEN ENDDATE - CURRDATE
ELSE DURATION ;
BEGIN/I9 = BEGDATE - &MinDate + 14;
COMPLETED/I9 = IF ENDDATE LT CURRDATE THEN DURATION
ELSE IF CURRDATE FROM BEGDATE TO ENDDATE THEN CURRDATE - BEGDATE
ELSE 0 ;
END
GRAPH FILE GANTT
SUM OFFSET
BEGIN
COMPLETED AS 'Completed'
REMAIN AS 'Remaining'
BY TASK
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setGraphType(25);
setTransparentFillColor(getSeries(0), true);
setTransparentBorderColor(getSeries(0), true);
setSeriesLabel(0, "");
setTransparentFillColor(getSeries(1), true);
setTransparentBorderColor(getSeries(1), true);
setSeriesLabel(1, "");
*END
*GRAPH_JS
-* The base date for JavaScript is 01/01/1970 and for WebFOCUS 31/12/1900
-* so we need to subtract the difference in days to obtain the correct basedate for our chart
-* and then convert it to milliseconds
yaxis: {
numberFormat: function(n){ var daynum = (n + &MinDate - 25203) * 24 * 60 * 60 * 1000;
var minDate = new Date(daynum);
return (minDate.getDate() + "/" + (minDate.getMonth() + 1) + "/" + minDate.getFullYear());
},
intervalMode: 'interval',
intervalValue: 28
},
series: [
{series: 'reset', tooltip: function(v, s, g) { return this.getGroupLabel(g) +
"<br/>" + v + " days " + this.getSeries(s).label;}}
],
referenceLines: [
{value: &CurrDate,
axis: 'y',
line: {color: 'red',
width: 1,
dash: '6 2 6'},
label: {text: 'Current Date:',
font: 'bold 8pt Sans-Serif',
color: 'red'},
anchor: 'end',
showValue: false
}
]
*END
END
-RUN
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
October 03, 2016, 04:48 PM
Waz
Nice.
Although, being on Linux, I had to change the case of GANTT.MAS