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,

Wouldn't the users of this information be in the best position to tell you what date to use?

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?

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.

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,

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.

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