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,

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

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..!

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.

I am struck..working though

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

thanks jimbo. that seem to be working.
how do I get the latest MTD value?

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

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

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