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] SALES TOTAL AND BY HIGHEST 3

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SALES TOTAL AND BY HIGHEST 3
 Login/Join
 
fenwicee
Member
posted
I'm trying to create what I thought would be a fairly simple report. All I am trying to do is show 5 columns: customer, sales for entire period and then sales for the latest three days. I have tried multiple things but I cannot seem to get this to work. Any suggestions?

This message has been edited. Last edited by: Kerry,
 
Posts: 3 | Location: Troy, MI | Registered: October 24, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
Have you tried a multi-verb request? I don't know what your period is but let's say that it is the current month. Here is some sample code, untested.
-SET &TESTDATE=20100429;
-SET &DAY3=AYMD(&TESTDATE,-3,'I8YYMD');
-TYPE &DAY3
-SET &CURRPER=EDIT(&TESTDATE,'999999');
-SET &BEGDT=&TESTDATE || '01';
DEFINE FILE whatever
DAYSALES3/D20=IF SALESDATE EQ &DAY3 THEN SALES ELSE 0;
DAYSALES2/D20=IF SALESDATE EQ AYMD(&DAY3,1,'I8YYMD') THEN SALES ELSE 0;
DAYSALES1/D20=IF SALESDATE EQ AYMD(&DAY3,2,'I8YYMD') THEN SALES ELSE 0;
END
TABLE FILE whatever
SUM SALES
BY CUSTOMER
SUM DAYSALES3 DAYSALES2 DAYSALES1
BY CUSTOMER
WHERE SALESDATE GE &BEGDT
END

I think you will have to tweak the Dialogue Manager code a bit to suit your needs and to handle your periods and month changes but this should give you an idea of what you need to do.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Or this:

SET ASNAMES=ON
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY

TABLE FILE GGORDER
SUM
QUANTITY AS 'TOT_QTY'
BY PRODUCT_CODE

SUM
QUANTITY
BY PRODUCT_CODE
BY HIGHEST 3 ORDER_DATE

ON TABLE HOLD
END

TABLE FILE HOLD
SUM TOT_QTY
BY PRODUCT_CODE

SUM QUANTITY
BY PRODUCT_CODE
ACROSS ORDER_DATE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
fenwicee
Member
posted Hide Post
I was actually able to get it to work using the DEFINE method.


DEFINE FILE SQLOUT
SALES0/D12.2=IF DATE1 EQ TO_DATE THEN SALES ELSE 0;
SALES1/D12.2=IF DATE1 EQ TO_DATE -1 THEN SALES ELSE 0;
SALES2/D20.2=IF DATE1 EQ TO_DATE - 2 THEN SALES ELSE 0;
END

TABLE FILE SQLOUT
SUM
SALES
SALES0
SALES1
SALES2
BY CUSTOMER


But now I am struggling with something new on this report. The TO_DATE field is a parameter in the SQL. I need the column heading on the SALES1 & SALES2 columns to reference the date they refer to. Any suggestions on this?
 
Posts: 3 | Location: Troy, MI | Registered: October 24, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
That's what the ACROSS is for.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
fenwicee
Member
posted Hide Post
That wasn't working. I had to go back and use a series of SET commands to end up getting it to work. thanks all though.
 
Posts: 3 | Location: Troy, MI | Registered: October 24, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Are we not curious about this "series of SET commands"?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report 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] SALES TOTAL AND BY HIGHEST 3

Copyright © 1996-2020 Information Builders