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?
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
April 07, 2011, 12:21 AM
TryFocus
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
April 07, 2011, 10:04 AM
Enigma006
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
April 07, 2011, 11:25 AM
TryFocus
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
April 07, 2011, 11:37 AM
Enigma006
I am struck..working though
8.1.05 HTML,PDF,EXL2K, Active, All
April 08, 2011, 03:31 PM
jimbo
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
thanks jimbo. that seem to be working. how do I get the latest MTD value?
8.1.05 HTML,PDF,EXL2K, Active, All
April 12, 2011, 02:15 PM
jimbo
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
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
April 13, 2011, 09:47 AM
jimbo
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