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     [CLOSED] Need report results to produce 3 separate values...WTD, MTD, & YTD
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Need report results to produce 3 separate values...WTD, MTD, & YTD
 Login/Join
 
Member
posted
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
 
Posts: 15 | Registered: June 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
@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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Need report results to produce 3 separate values...WTD, MTD, & YTD

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