Focal Point
[CLOSED] Need report results to produce 3 separate values...WTD, MTD, & YTD

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8217054196

July 31, 2019, 10:44 AM
RC@WB
[CLOSED] Need report results to produce 3 separate values...WTD, MTD, & YTD
Need my report results to produce 3 separate values...WTD, MTD, & YTD. DATE is format A10.
Using App Studio 8201M

DATE Dollars

2018/12/29 $2,000
2018/12/30 $2,030
2018/12/31 $2,250
2019/01/01 $2,452

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


WebFOCUS 8

Windows, All Outputs
July 31, 2019, 11:06 AM
BabakNYC
1. You have to create a real date out of the A10 field using EDIT and date functions.
2. You can create variables giving you the date for any of the starting points you want.
3. Use the variables you need to calculate the value in a DEFINE.

The variables have to be set up prior to your report's TABLE FILE...
Here are some examples of the variables you'll need to create.
  
-SET &&CURDATE=&YYMD;
 
-* This sets what Yesterday is:
-SET &&YESTERDAY=AYMD(&&CURDATE,-1,'I8YYMD');
-SET &&PAST35D=AYMD(&&CURDATE,-35,'I8YYMD'); 
-* This sets Current Year based upon Yesterday
-SET &&CYEAR=EDIT(&&YESTERDAY,'9999');
 
-* This sets Prior Year based upon Yesterday
-SET &&PYEAR=&&CYEAR-1;
 
-* This sets what the current month is based upon todays's date
-SET &&CMONTH=EDIT(&&CURDATE,'999999') || '01';
-SET &CMONTHYYM=EDIT(&&CURDATE,'999999');
-* This sets current quarter
-SET &&CQTR=IF EDIT(&&YESTERDAY,'$$$$99$$') LE '03' THEN EDIT(&&YESTERDAY,'9999') || ' ' | 'Q1' ELSE
-           IF EDIT(&&YESTERDAY,'$$$$99$$') LE '06' THEN EDIT(&&YESTERDAY,'9999') || ' ' | 'Q2' ELSE
-           IF EDIT(&&YESTERDAY,'$$$$99$$') LE '09' THEN EDIT(&&YESTERDAY,'9999') || ' ' | 'Q3' ELSE
-           EDIT(&&YESTERDAY,'9999') || ' ' | 'Q4';
 
-* This sets what last month is based upon today's date
-SET &&PMONTH=EDIT(AYM(&CMONTHYYM, -1, 'I6YMD')) || '01';
-SET &PMONTHYYM=EDIT(&&PMONTH,'999999');
 
-* This sets 7 days prior based upon &YESTERDAY
-SET &&P7DAY=AYMD(&&YESTERDAY,-7,'I8YYMD');
 
-* This sets prior 3 months based upon last month - it will generate complete data
-SET &&P3MONTH=EDIT(AYM(&PMONTHYYM, -1,'I6YYM')) || '01';
 
-* This sets prior 6 months based upon last month - it will generate complete data
-SET &&P6MONTH=EDIT(AYM(&PMONTHYYM, -4,'I6YYM')) || '01';
 
-* This sets prior 12 months based upon last month - it will generate complete data
-SET &&P12MONTH=EDIT(AYM(&PMONTHYYM, -12,'I6YYM')) || '01';
 
-* This sets the first day of the calendar year
-SET &&YTDSTART=EDIT(&&YESTERDAY,'9999') || '0101';



WebFOCUS 8206, Unix, Windows
July 31, 2019, 07:46 PM
Hallway
You can use some of the newer simplified date functions, like DTRUNC, to simplify the process. This seemed to work for me:
  
DEFINE FILE WF_RETAIL_TINY
-* Seting current date back for this example so that there is data in the REVENUE_US measure. 
-* Change last parameter in DTADD to -1 for yesterdays date.
NOW_DT/wrMtrDYY=DTADD( DT_CURRENT_DATE(), DAY, -320);

-* the current YEAR beginning date based on NOW_DT
NOW_YR_BEG/MDYY=DTRUNC( NOW_DT, YEAR );

-* the current MONTH beginning date based on NOW_DT
NOW_MO_BEG/MDYY=DTRUNC( NOW_DT, MONTH );

-* the current WEEK beginning date based on NOW_DT
NOW_WK_BEG/MDYY=DTRUNC( NOW_DT, WEEK );

-* the Date as A10
DT_A10/A10=EDIT2( TIME_DATE, 'YYMD' );

-* Create a DATE field from the A10 Date
DT_YYMD/YYMD=DATECVT( EDIT( DT_A10, '9999$99$99'), 'A8YYMD','YYMD');

-* the YEAR beginning date for each DATE field
DT_YR_BEG/YYMD=DTRUNC( DT_YYMD, YEAR );

-* the MONTH beginning date for each DATE field
DT_MO_BEG/YYMD=DTRUNC( DT_YYMD, MONTH );

-* the WEEK beginning date for each DATE field
DT_WK_BEG/YYMD=DTRUNC( DT_YYMD, WEEK );

-* compare the YEAR beginning date of the DATE field and current value
REVENUE_YTD/D12.2M=IF DT_YR_BEG EQ NOW_YR_BEG THEN REVENUE_US ELSE 0;

-* compare the MONTH beginning date of the DATE field and current value
REVENUE_MTD/D12.2M=IF DT_MO_BEG EQ NOW_MO_BEG THEN REVENUE_US ELSE 0;

-* compare the WEEK beginning date of the DATE field and current value
REVENUE_WTD/D12.2M=IF DT_WK_BEG EQ NOW_WK_BEG THEN REVENUE_US ELSE 0;
END
TABLE FILE WF_RETAIL_TINY
SUM 
    REVENUE_YTD AS 'Revenue,YTD' 
    REVENUE_MTD AS 'Revenue,MTD' 
    REVENUE_WTD AS 'Revenue,WTD' 
BY HIGHEST DT_YYMD/MDYY AS 'Date'
HEADING
"Current Totals to Date as of:"
"<NOW_DT"
"YTD:<TOT.REVENUE_YTD"
"MTD:<TOT.REVENUE_MTD"
"WTD:<TOT.REVENUE_WTD"
WHERE DT_YYMD GE NOW_YR_BEG;
WHERE DT_YYMD LE NOW_DT;
ON TABLE PCHOLD FORMAT HTML
ON TABLE RECOMPUTE
ON TABLE SET CENT-ZERO ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/flat.sty, $
TYPE=REPORT, LINES-PER-PAGE=UNLIMITED, HFREEZE=ON, SCROLLHEIGHT=AUTO, $
TYPE=HEADING, FONT='CALIBRI', SIZE=12, COLOR=RGB(75 75 75), STYLE=BOLD, JUSTIFY=LEFT, HEADALIGN=BODY, $
TYPE=HEADING, LINE=1, SIZE=11, COLSPAN=4, STYLE=ITALIC, JUSTIFY=LEFT, $
TYPE=HEADING, LINE=2, SIZE=11, COLSPAN=4, STYLE=ITALIC, JUSTIFY=LEFT, $
TYPE=HEADING, LINE=3, ITEM=2, COLSPAN=3, $
TYPE=HEADING, LINE=4, ITEM=2, COLSPAN=3, $
TYPE=HEADING, LINE=5, ITEM=2, COLSPAN=3, $
ENDSTYLE
END



Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
August 12, 2019, 05:07 PM
Hallway
I see that you marked this as [CLOSED], and not [SOLVED].

Did you not find a solution to your question? Did these options not help?

🤔

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
August 13, 2019, 07:28 AM
MartinY
@Hallway,

It is Chuck that has changed the status to [CLOSED] (see first post for last updated by)
After a while if there is no more action on a post the moderator change its status to [CLOSED] when no status already there


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
August 13, 2019, 11:31 AM
Hallway
quote:
Originally posted by MartinY:
@Hallway,

It is Chuck that has changed the status to [CLOSED] (see first post for last updated by)
After a while if there is no more action on a post the moderator change its status to [CLOSED] when no status already there


Interesting. I would think that if a moderator is changing the status of a post, that they should also make a post stating that they are doing so.

IDK, maybe maybe that's just me...

😒


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs: