MTD

[#1]

Please help me out,

I am trying to calculate MTD productwise, have used 2 expressions for tht but getting no results,

Please some one suggest better way of doing this,

1st MTD expression : =count({$<ap_year = {$(vThisYear)},ap_month = {$(vThisMonthText)}>}FILE_NO)

2nd MTD expression :

=Count({$<MonthID = {$(=Max(MonthID))},

Year = ,

Qtr = ,

Month = >}FILE_NO)

can anybody let me knw wht wrng m doing or better way of doing this,

this is the table i have defined to acheive this,

Load Distinct
FILE_NO,
date(Commondate,'DD/MMM/YYYY') as Linkdate,
CommonBranch,
CommonProduct,
CommonProduct as LOAN_PROD_CODE,
CommonServiceCentre,
CommonServiceCentre as SERVICE_CENTRE_CODE,
year(Commondate) as Year,
Month(Commondate) as Month,
AutoNumberHash128(FILE_NO,Commondate) As Link_Field,
month(Commondate) as ap_month,
Year(Commondate) as ap_year,
//Year(Linkdate) as Year,
day(Commondate) as day,

if(wildmatch(month(Commondate),'Jan','Feb','Mar'),num(year(Commondate)),num(year(Commondate))+1) as Commondate_FIN_YEAR,

       if(Wildmatch(num(Month(Commondate)),1,2,3),num(Month(Commondate))+9,num(Month(Commondate))-3) as Commondate_FIN_MONTH,

       if(month(Commondate)=1 or month(Commondate)=2 or Month(Commondate)=3,'Q4',
         IF(month(Commondate)=4 or month(Commondate)=5 or month(Commondate)=6,'Q1',
         IF(month(Commondate)=7 or month(Commondate)=8 or month(Commondate)=9,'Q2',
         IF(month(Commondate)=10 or month(Commondate)=11 or month(Commondate)=12,'Q3')))) AS Commondate_FIN_QRTR,
         yearname(Commondate,0,4) as FiscalYear,
         Quartername(Commondate,0,4) as FiscalQtr,
//         ----------------------------------------------------
         month(Commondate) & '-' & right(year (Commondate),2) as MONTH_YEAR,
year(Commondate) as [COMMON YEAR],
    If(InYearToDate (Commondate,$(vdate), 0,4),1,0) as [TYTD Flag],//   Current Year to Date this Year
If(InYearToDate (Commondate,$(vdate), -1,4),1,0) as [LYTD Flag],//  Last Year to Date this Year
   If(InMonthToDate(Commondate,$(vdate), 0,4),1,0) as [TY CMTD Flag],//   Current Month to Date this Year
If(InMonthToDate(Commondate,$(vdate), -1,4),1,0) as [TY PMTD Flag],//  Previous Month to Date this Year
If(InMonthToDate(Commondate,DATE(ADDMONTHS($(vdate),-12)), 0,4),1,0) as [LY CQTD FLAG], // ALL DATES CURRENT QUTER LAST YEAR
If(InMonthToDate(Commondate,Date(addmonths($(vdate),-12)), 0,4),1,0) as [LY CMTD Flag],//  Current Month to Date Last Year
If(InMonth(Commondate, Date(addmonths($(vdate),-12)), 0,4),1,0) as  [LY CM Flag], //  All Dates In Current Month Last Year
If(InMonth(Commondate, Date(addmonths($(vdate),-12)), -1,4),1,0) as  [LY PM Flag], //  All Dates In Previous Month Last Year
If(InMonth(Commondate, $(vdate),-1,4),1,0) as [TY PM Flag], //  All Dates In Previous Month         

year(yearname(Commondate,0,4)) as [FIN YEAR],
Month(Commondate) as [COMMON MONTH],
DAY(Commondate) as [COMMON DAY],
num(Month(Commondate))  as Month_No,
'' as test,

year(Linkdate)-1*12 + Num(Month(Linkdate))) as MonthID

AUTONUMBER(NUM(FILE_NO) & '-' & Commondate  & CommonServiceCentre & CommonBranch & CommonProduct) as Loan_Key

Resident CommonInfoTemp
WHERE Commondate <= makedate(year($(vdate)),month($(vdate)),day($(vdate)));

[#2]

try this

Sum({<ap_year = {$(vThisYear)}, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} FILE_NO)