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     [CLOSED] Create a report that will display revenue between a start and end date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Create a report that will display revenue between a start and end date
 Login/Join
 
Gold member
posted
Hi all,

I have 3 different reports on an HTML. One is showing bookings by a close date. Once we close a deal we notice it as a closed won deal, so I'm displaying that money on the closed day, as a whole amount. That's easy.

Now the requirements are to take that whole booking amount and display it between a revenue start date and estimated end date. They want to recognize the revenue by months, quarters and years of how long the project will be. I have no clue how to do this. I'm not sure what date to display in the report to categorize the information. Any help would be greatly appreciated. Please let me know if I need to clarify anything.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Virtuoso
posted Hide Post
Wouldn't the users of this information be in the best position to tell you what date to use?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gold member
posted Hide Post
Thank you for your response.

I figured I'd get this question. lol My question is basically.. How do I show by month, quarter, and year between 2 dates? We have data between a start and end date, but what date could I use as the actual 'bucket' for the lack of a better term. I think my boss said something to the fact, we don't have a 'continual date' to display. In one report I'm using the closed date as the bucket but that wouldn't be the case in this report.. Does that help?


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Virtuoso
posted Hide Post
You can decompose any date field using a DEFINE. Look up DPART. For instance, if you have field called CLOSE_DATE you can get the Year, Quarter and Month of that date using this syntax:
DTPART(CLOSE_DATE, YEAR), DTPART(CLOSE_DATE, QUARTER), DTPART(CLOSE_DATE, MONTH)

Then Sort by any of the new fields depending on what you're trying to show.

Regarding Start and End Dates, I assume you have those either in a variable or you've put them in another field. Limiting the output can be accomplished with a WHERE XYZ_DATE GT startdate and so on.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gold member
posted Hide Post
Lets see if I can clarify better, we have an opportunity(OptyA), a start date (1/2018) and end date (11/2018), and a value for that opportunity (11k). I’m doing a date diff between the start and end date that equals ‘11’ months between the 2 dates. Now I want to divide the value, 11k, by the date diff '11' months which equals 1lk. We have that calculation. But now we need to diplay that value across the 11 months across the 11 months. 1/2018, 02,2018 etc. etc.

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 88 | Registered: December 06, 2016Report This Post
Virtuoso
posted Hide Post
zcbillions

Sounds like you need to create a HOLD file with the new calculated number by months and then run the report from the HOLD file.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Expert
posted Hide Post
Despite creating a new post about (essentially) the same as your previous one on McGyver, I'll give you a snippet of code that I wrote many years ago which shows how you might achieve your task.

This only spits out a split value for days within a month, but it should give you enough to be able to work out the other elements that you need.

-DEFAULT &YEAR=1996

SET ASNAMES   = ON
SET HOLDLIST  = PRINTONLY
SET CENT-ZERO = ON

-* The LRECL in the next line determines how many rows within the McGyver file will be available
-* and therefore controls the range of dates that you can utilise.
-* Here, because 1996 was a leap year, the limit is all dates within 1996, all 366 of them
FILEDEF FOCMCGYV1 DISK FOCCACHE/FOCMCGYV1.DAT (LRECL 366 RECFM F
-*
EX -LINES * EDAPUT MASTER,focmcgyv1,CV,FILE
FILENAME=focmcgyv1, SUFFIX=FIX
SEGNAME=SEG01, $
FIELDNAME=MCGYVER_KEY, ,A1,A1,$
SEGNAME=SEG02, PARENT=SEG01, POSITION=MCGYVER_KEY, OCCURS=VARIABLE, $
FIELDNAME=CHAR, ,A1,A1, $
FIELDNAME=CTR,ORDER,I4,I4, $
EDAPUT*
-RUN

-WRITE FOCMCGYV1 x
-RUN

TABLE FILE FOCMCGYV1
  SUM COMPUTE XCN_DATE/HYYMDs = IF LAST CTR EQ 0 THEN '&YEAR/01/01 00:00:00' ELSE HADD(LAST XCN_DATE, 'DAY', 1, 8, 'HYYMDs');
      COMPUTE XCN_DMYY/DMYY   = HDATE(XCN_DATE, 'YYMD');
      COMPUTE XCN_DAY/D       = HDATE(XCN_DATE, 'YYMD');
      COMPUTE XCN_MNTH/MtYY   = HDATE(XCN_DATE, 'YYMD');
   BY CTR -*NOPRINT
   BY MCGYVER_KEY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS mcgyver FORMAT FOCUS INDEX MCGYVER_KEY
END
-RUN

JOIN CLEAR *
JOIN X WITH PRODUCT IN GGSALES TO MULTIPLE MCGYVER_KEY IN MCGYVER AS J1

DEFINE FILE GGSALES
  X/A1 WITH PRODUCT = 'x';
  MNTH/YYM = DATE;
END

TABLE FILE GGSALES
   SUM DOLLARS
       MAX.XCN_DAY NOPRINT
	BY MNTH    AS 'Month'
	BY REGION
   SUM DOLLARS NOPRINT
       COMPUTE PORTION/D20.8c = C3 / C2;
	BY MNTH
	BY REGION
    BY XCN_DMYY AS 'Date'
WHERE XCN_MNTH EQ MNTH
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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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     [CLOSED] Create a report that will display revenue between a start and end date

Copyright © 1996-2020 Information Builders