[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,452This 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.