topic Re: Prior Year MTD and Prior Year QTD in QlikView

Prior Year MTD and Prior Year QTD

kishoreravi1983 — Wed, 03 May 2017 09:26:33 GMT

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

Re: Prior Year MTD and Prior Year QTD

awhitfield — Wed, 03 May 2017 11:37:54 GMT

Hi Ravi,

try out the following link:

YTQ, QTD, MTD and WTD

Andy

Re: Prior Year MTD and Prior Year QTD

rahulpawarb — Wed, 03 May 2017 11:40:44 GMT

Hello Ravi,

Please refer below link:

Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)

Hope this will be helpful.

Regards!

Rahul

Re: Prior Year MTD and Prior Year QTD

sunny_talwar — Wed, 03 May 2017 11:45:09 GMT

May be flag like these... not sure what is the purpose of month(FDM)&' '&year(FDM):

If(FDM >= MonthStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior MTD Flag],

If(FDM >= QuarterStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior YTD Flag],

If(FDM >= YearStart(Today(), -1) and FDM <= AddMonth(Today(), -1), 1, 0) as [Prior QTD Flag]

Re: Prior Year MTD and Prior Year QTD

kishoreravi1983 — Thu, 04 May 2017 06:33:59 GMT

Thanks Sunny for your reply.

month(FDM)&' '&year(FDM) this is to display the Value in Given format.

Re: Prior Year MTD and Prior Year QTD

kishoreravi1983 — Thu, 04 May 2017 06:46:10 GMT

Hi Sunny, I am not getting the Right Result. Prior QTD FLAG should give me 2016, Q1 and Q2

Prior MTD should give me 2016 April or May, which ever is my current month in 2017

Can you plz relook if possible.

Thanks,

Ravi

Re: Prior Year MTD and Prior Year QTD

kishoreravi1983 — Thu, 04 May 2017 06:49:11 GMT

Hi Andy,

This Was nice Post in term of Writing has an Expression.

But i would like to Derive it at Script level. I am not giving the user any selection.

Regards,

Ravi

Re: Prior Year MTD and Prior Year QTD

sunny_talwar — Thu, 04 May 2017 11:05:23 GMT

Got it... try this

If(FDM >= MonthStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior MTD Flag],

If(FDM >= QuarterStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior QTD Flag],

If(FDM >= YearStart(AddYears(Today(), -1)) and FDM <= AddYears(Today(), -1), 1, 0) as [Prior YTD Flag]

Re: Prior Year MTD and Prior Year QTD

kishoreravi1983 — Thu, 04 May 2017 16:19:52 GMT

Thanks Sunny..It was very Helpful.

Have a great day!

Re: Prior Year MTD and Prior Year QTD

kishoreravi1983 — Sun, 04 Jun 2017 07:25:32 GMT

Hi Sunny,

Hope you are doing good.

I am stuck with an Issue with Prior QTD.

Actually as per financial Year i am in Q3. First month

If(FirstDayofMonth >= QuarterStart(AddYears(Today(), -1)) and FirstDayofMonth <=Quarterend(AddYears(Today(), -1)), 1, 0) as [Prior QTD Flag],

But when am using this Prior QTD Flag it is still calculating for 2016-Q2 where has its should show me 2016-Q3 1 Month Data.

How do i write an Expression or short out this situation.

Please help me out. If you are free we can connect over the web or Go to meeting link for small call.

Regards,

Ravi

Re: Prior Year MTD and Prior Year QTD

sunny_talwar — Sun, 04 Jun 2017 16:54:21 GMT

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

Best,

Sunny

Re: Prior Year MTD and Prior Year QTD

goro2010 — Mon, 05 Jun 2017 06:25:42 GMT

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";