Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] trying to do an avg after a date field across

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] trying to do an avg after a date field across
 Login/Join
 
DaveZ
Gold member
posted
trying to do an average after a using the date field across, and to format the date field MDYY with time.

code follows

ENGINE SQLORA SET DEFAULT_CONNECTION prodarch
SQL SQLORA PREPARE SQLOUT FOR
select sv.partno, sv.team, sum(qty_due) as qty_due, sv.SD_WEEK_END from wf_sales_view sv
where sv.PLANT_CODE = '1'
and sv.SCHED_DATE > trunc(sysdate)
and sv.SCHED_DATE <= trunc[sysdate + 90)
group by sv.PARTNO, sv.TEAM, sv.SD_WEEK_END
END
TABLE FILE SQLOUT
PRINT
QTY_DUE/D5 AS 'QTY_DUE'
BY PARTNO
BY TEAM
ACROSS SD_WEEK_END
COMPUTE 13WKAVG/D4.2 = QTY_DUE/13; AS 'WEEKLY AVG'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

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


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
GamP
Virtuoso
posted Hide Post
To do this kind of averaging it might be best to do that with a so called multi verb request.
Exmaple for your situation might be:
TABLE FILE SQLOUT
SUM AVE.QTY_DUE AS 'WEEKLY AVG'
BY PARTNO
BY TEAM
PRINT QTY_DUE/D5 AS 'QTY_DUE'
BY PARTNO
BY TEAM
ACROSS SD_WEEK_END
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
That way your average will always be 'correct'. You do not know for sure what the actual number of weeks will be, so dividing by 13 may not be necessarily correct. Besides, you would need the row-total to do your average, which you don't get with the compute.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
DaveZ
Gold member
posted Hide Post
Thanks that worked,
I still have two other problems I can't format the date properly and the data cascades down, how do I keep the qty due all on the same row

like this
Week End
2010/11/21 00:00:00 2010/11/28 00:00:00 2010/12/05 00:00:00
Partno Team Weekly Avg QTY DUE QTY_DUE QTY_DUE
P00983 T015 2.86 0
4
0


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
GamP
Virtuoso
posted Hide Post
The first is probably due to the fact that your field (SD_WEEK_END) is a DATE/TIME field. You'd have to convert it to a YYMD field. You can do that with the HDATE function. Look up it's purpose and use in the manual. For the cascading thing, since you;re on 713 you can't use the new setting for that, but you have to resort to using the SUM verb i.s.o. the PRINT.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
DaveZ
Gold member
posted Hide Post
We are on 7.7.01


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
DaveZ
Gold member
posted Hide Post
I don't know where within the code to do the hdate I tried it in serveral places with no success I assumed it should be on the across like this

ACROSS COMPUTE WEEK_END/YYMD=HDATE(WEEK_END, 'YYMD');

but I and got errors.


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
GamP
Virtuoso
posted Hide Post
quote:
We are on 7.7.01

Then update your profile please, it still says 713...
The HDATE is best done in a define. Then you can use the defined field as the across field.
Alternative is to change the master file. Currecntly it probably has something like format=HYYMDs and actual=HYYMDs. You can change that to read format=YYMD and actual=DATE. Only do this when there will not ever be the need of the time portion of the field. And remember to manually change this when the master file get refreshed...

The setting for 7701 is SET ACROSSPRT=COMPRESS, or use SUM iso PRINT.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
DaveZ
Gold member
posted Hide Post
First and foremost thanks for all the help and ideas. I did create a view then a master file from the sql code and defined the date as per our standards MDYY DATE, and it works just fine. But I will go back and try the command you gave me.

Once again thanks


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] trying to do an avg after a date field across

Copyright © 1996-2020 Information Builders