topic Re: MTD Flag help in QlikView

MTD Flag help

satishkurra — Mon, 26 Jun 2017 16:37:14 GMT

I'm trying to derive MTD Flag for Current Year and also for Last Year.

MTD Flag is from Current Year Month Start Date to Last Sunday

PMTD Flag is Previous Year Month Start Date to Last Sunday in Previous Year

Ex:

MTD Flag : 6/1/2017 - 6/25/2017

PMTD Flag: 6/1/2016 - 6/26/2016

MTD Flag is working but PMTD Flag is ranging from 6/1/2016 - 6/19/2016

I'm using below logic

If(MonthName(Today())=MonthName(Date) and Date >= MonthStart(Today(),0) and Date <= WeekEnd(Today(),-1,0), 1, 0) as MTDFlag,

if(Date >= MonthStart(AddYears(Today(),-1),0) and Date <= WeekEnd(AddYears(Today(),-1),-1,0), 1, 0) as PMTDFlag,

Request to please help

Thanks

Satish

sunny_talwar — Mon, 26 Jun 2017 16:50:38 GMT

May be this

if(Date >= MonthStart(AddYears(Today(),-1),0) and Date <= WeekEnd(AddYears(Today(),-1),0,0), 1, 0) as PMTDFlag,

satishkurra — Mon, 26 Jun 2017 16:55:59 GMT

Thanks Sunny for your response

Will it work every week going forward

The reason behind the ask is i'm performing this check on Monday which is why i'm setting -1 to get Last day of WeekEnd which is Sunday every time

satishkurra — Mon, 26 Jun 2017 17:09:16 GMT

Also i do have problem with Previous Year Week Start Date

WeekStart(AddYears(Today(),-1),0,0)

sunny_talwar — Mon, 26 Jun 2017 17:10:06 GMT

So next week the <= part will be 07/03/2017... if that is correct, then you have nothing to worry about

sunny_talwar — Mon, 26 Jun 2017 17:10:31 GMT

Not sure I understand?