Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SALES TOTAL AND BY HIGHEST 3
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SALES TOTAL AND BY HIGHEST 3
 Login/Join
 
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, 2006Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SALES TOTAL AND BY HIGHEST 3

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.