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] MTD and YTD calculation - URGENT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] MTD and YTD calculation - URGENT
 Login/Join
 
Master
posted
Hai

I am working on a report with lot of calculations in it. I completed most part of it and am at the ending where I need to calculate YTD and MTD. Data is like this. Can someone help me how to do this?

  

VAL   DATE    T1     T2  P%

COMP1 2011/01 $2,000 $24 94.7% 
COMP1 2011/02 $2,030 $22 97.7% 
COMP1 2011/03 $2,250 $26 102.4% 
COMP1 2011/04 $2,452 $24 103.8% 



Data is this format above. Date is in YYM format. For each VAL based on DATE field, I need to calculate YTD and MTD of T1, T2 and P%. How do I do this?

Thanks in advance.

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


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Gold member
posted Hide Post
Try Below code, Hope this solves your problem.

  
DEFINE FILE CAR
EDATE/YYM = '2008/04';
TODAY_DATE/YYMD = &YYMD;
NEW_HIRE_DATE/YYMD = DATECVT(EDATE, 'YYM', 'YYMD');
NO_OF_YEARS/I8 = DATEDIF(NEW_HIRE_DATE, TODAY_DATE, 'Y');
NO_OF_MONTHS/I8 = DATEDIF(NEW_HIRE_DATE, TODAY_DATE, 'M');
END
-*
TABLE FILE CAR
PRINT  
EDATE
TODAY_DATE
NEW_HIRE_DATE
NO_OF_YEARS
NO_OF_MONTHS
BY COUNTRY
END


Prod: WF 7.6.10 windows. -- MRE/Dashboard/Self Service/ReportCaster - Windows XP
 
Posts: 82 | Location: Chicago | Registered: September 28, 2005Report This Post
Master
posted Hide Post
Thanks for your reply. This doesn't help me much I guess. When I put EDATE as 2011/04. I get 0 years and 0 months..!


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Gold member
posted Hide Post
If you get 0 years & 0 months. Use IF Condition to bump up the value. ( I guess you might have to implement this logic for rest of Year, Month counts).

Hope this helps.


Prod: WF 7.6.10 windows. -- MRE/Dashboard/Self Service/ReportCaster - Windows XP
 
Posts: 82 | Location: Chicago | Registered: September 28, 2005Report This Post
Master
posted Hide Post
I am struck..working though


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Silver Member
posted Hide Post
If I understand this correctly you want 2 new columns for t1(YTD), t2(YTD) & p%. Leaving the p% column out of this for now since perhaps you are computing this percentage against some other field you can compute the YTD using LAST

SUM T1
    COMPUTE
    T1_YTD/D5M = T1 + LAST T1_YTD ;
BY VAL
BY DATE
END
    


I believe this should give you what you want


7.7.04
Win2K3, Unix
Oracle 10G,SQL2K,XFOCUS,ESRI,BID,MRE,SELF-SERVICCE
 
Posts: 36 | Location: Melville,NY | Registered: August 09, 2004Report This Post
Master
posted Hide Post
thanks jimbo. that seem to be working.
how do I get the latest MTD value?


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Silver Member
posted Hide Post
well it would seem that you want a report by period reprsnting all of the periods within the current year. That will include the valuew for that period (t1 and t2) plus columns representing their cumulative YTD totals through that period (which is the compute I provided previously). The way I understand your requirements T1 is MTD and the compute would be YTD.
  SUM T1 AS 'MTD_T1'
    COMPUTE
    T1_YTD/D5M = T1 + LAST T1_YTD ; AS 'YTD_T1'
BY VAL
BY DATE


7.7.04
Win2K3, Unix
Oracle 10G,SQL2K,XFOCUS,ESRI,BID,MRE,SELF-SERVICCE
 
Posts: 36 | Location: Melville,NY | Registered: August 09, 2004Report This Post
Master
posted Hide Post
Ok. This is my final query.
  
TABLE FILE TABLENAME
SUM
	T1 OVER
	T2 OVER
	T3 OVER
	T4
BY STATE
BY CITY
ACROSS DATE_YYMM
END


DATE_YYMM (in format YYMM) has 4 months of data (till date), I get 4 columns. But I need only 2 columns with MTD (only 2010/04 data) and YTD (sum of all 4 months data) values for all 4 fields (T1, T2, T3 and T4).
Hope this makes sense..

Thanks


8.1.05
HTML,PDF,EXL2K, Active, All
 
Posts: 484 | Registered: February 03, 2009Report This Post
Silver Member
posted Hide Post
so put in som DM and a DEFINE. Lets assume you want current month MTD and YTD through the current month
-* THIS WILL SET THE CURRENT PERIOD
-SET &PERIOD  = EDIT(&YYMD,'999999') ;
-SET &YRSTART = EDIT(PERIOD,'999901') ;
DEFINE FILE MyFile
T1_MTD/D12 = IF DATE_YYMM EQ '&PERIOD' THEN T1 ELSE 0 ;
END
TABLE FILE MyFile
IF DATE_YYMM FROM '&YRSTART' TO '&PERIOD'
SUM T1_MTD AS 'MTD'
    T1     AS 'YTD'  
BY whatever
end


7.7.04
Win2K3, Unix
Oracle 10G,SQL2K,XFOCUS,ESRI,BID,MRE,SELF-SERVICCE
 
Posts: 36 | Location: Melville,NY | Registered: August 09, 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] MTD and YTD calculation - URGENT

Copyright © 1996-2020 Information Builders