Prior Year MTD and Prior Year QTD

[kishoreravi1983]

Hi All,

Can Some one help me with below expression how to derive Prior MTD and Prior QTD.

FDM= First Day of Month

below expression is giving me Prior YTD.

if(FDM >= AddYears(YearStart(today()),-1) and FDM <= AddYears(addmonths(MonthEnd(today()),-1),-1),month(FDM)&' '&year(FDM)) as [Prior YTD],

any Pointers are helpful.

Regards,

Ravi

[sunny_talwar]

It would be helpful if you can share a sample which shows the problem you are seeing.

Best,

Sunny

[goro2010]

Please find my Calendar script below for all your needs:

"Calendar":

LOAD [Date],

  Day([Date]) AS [Day],

  WeekDay([Date]) AS [WeekDay],

  Week([Date]) AS [WeekOfYear],

// Week([Date]) AS [Week], 

  WeekYear([Date]) AS [WeekYear], //Added 2012-05-15 - To fix week of month

  Month([Date]) AS [Month],

  Year([Date]) AS [Year],

  Month(Date([Date],'DD/MM/YYYY')) AS [Month Name],

  Date(MonthStart([Date]),'MMM/YY') AS [MonthFiscal],

  Date(YearStart([Date],0,0),'YYYY') AS [YearFiscal],

  Date([Date],'MM-YYYY') AS [YearMonth],

  Year([Date])*100+Month([Date]) AS [YearMonth2],   //Added 2012-05-15 - To fix week of month

  'Q' & Ceil(Month([Date])/3) AS [Quarter],

  Year([Date]) & '/' & 'Q' & Ceil(Month([Date])/3) AS [YearQuarter],

  Num(WeekDay([Date])) AS [Weekday#],

  YearToDate([Date], 0,1) AS [CurrentYTDFlag],

  YearToDate([Date],-1,1) AS [LastYTDFlag],

//=================================================LEAP YEAR=========================================================================//

  If(Mod(Date(YearStart([Date],1,0),'YYYY'),4) = 0 AND Mod(Date(YearStart([Date],1,0),'YYYY'),100) <> 0

   OR Mod(Date(YearStart([Date],1,0),'YYYY'),400) = 0,'Yes','No') AS [LeapYearFlag],

  If(Year([Date]) = (Year(Today())), 1, 0) AS [CY_Flag],

  If(Year([Date]) = (Year(Today())-1), 1, 0) AS [LY_Flag]

RESIDENT  "NonConformity"

;

"WeekOfMonth":

LOAD DISTINCT

  [YearMonth2] AS [T1],

  [WeekOfYear] AS [T2],

  [Date] AS [T3]

// If (isnull(peek("WeekOfYear")),1, if (peek("WeekOfYear") <> WeekOfYear, peek("MonthCount")+1,peek("MonthCount"))) as WeekOfMonth,

// If(YearMonth=Peek('T1',-1) And WeekOfYear=Peek('T2',-1)+1,Peek('WeekOfMonth',-1)+1,1) AS [WeekOfMonth]

RESIDENT "Calendar"

ORDER BY [YearMonth2], [Date]//, [WeekOfYear]

;

"New":

NOCONCATENATE LOAD DISTINCT

  T1, //AS [YearMonth],

  T2, //AS [WeekOfYear],

  //T3,

  If(T1=Peek('T1',-1), If(T2=Peek('T2',-1),Peek('WeekOfMonth',-1),Peek('WeekOfMonth',-1)+1) ,1) AS [WeekOfMonth],

  'Week ' & If(T1=Peek('T1',-1), If(T2=Peek('T2',-1),Peek('WeekOfMonth',-1),Peek('WeekOfMonth',-1)+1) ,1) AS [WeekOfMonthCode]

RESIDENT "WeekOfMonth";

DROP TABLE "WeekOfMonth";

LEFT JOIN ("Calendar")

//NOCONCATENATE

LOAD

  [T1] AS YearMonth2,

  [T2] AS WeekOfYear,

  [WeekOfMonth],

  [WeekOfMonthCode]

RESIDENT "New";

DROP TABLE "New";