Calculate QTD (Quarter to date) flag

[intapiuser]

This is the solution of how to calculate the QTD, in continuation of the article : Calculate MTD, QTD and YTD Values

If the dim date is a base table you'll need to add the following 3 invisible fields:

1. FirstDayofQ:

CASE WHEN GetQuarter(Date) = 1 THEN CreateDate(getyear(Date),1,1)
WHEN GetQuarter(Date) = 2 THEN CreateDate(getyear(Date),4,1)
WHEN GetQuarter(Date) = 3 THEN CreateDate(getyear(Date),7,1)
WHEN GetQuarter(Date) = 4 THEN CreateDate(getyear(Date),10,1) END 

2. QTDMonthInd:

CASE WHEN GetMonth(Date) IN(1,4,7,10) THEN 1
WHEN GetMonth(Date) IN(2,5,8,11) THEN 2
WHEN GetMonth(Date) IN(3,6,9,12) THEN 3 END

3. QTDMonthIndNow:

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1
WHEN GetMonth(now()) IN(2,5,8,11) THEN 2
WHEN GetMonth(now()) IN(3,6,9,12) THEN 3 END

Now add the isQTD field which is the actual flag you would use:

CASE WHEN 
(QTDMonthInd < QTDMonthIndNow and x.Date >= FirstDayofQ) or 
(QTDMonthInd = QTDMonthIndNow AND getday(Date)<=getday(now())) 
THEN 1 ELSE 0 END

If the dim date is already a custom table then you would need to use and modify this code:

select x.date,
CASE WHEN 
(x.QTDMonthInd < x.QTDMonthIndNow and x.date >= x.FirstDayofQ) or 
(x.QTDMonthInd = x.QTDMonthIndNow AND getday(x.date)<=getday(now())) 
THEN 1 ELSE 0 END AS isQTD

from (
select distinct date,
CASE WHEN GetQuarter(a.Date) = 1 THEN CreateDate(getyear(a.Date),1,1)
WHEN GetQuarter(a.Date) = 2 THEN CreateDate(getyear(a.Date),4,1)
WHEN GetQuarter(a.Date) = 3 THEN CreateDate(getyear(a.Date),7,1)
WHEN GetQuarter(a.Date) = 4 THEN CreateDate(getyear(a.Date),10,1)
END AS FirstDayofQ,

CASE WHEN GetMonth(a.Date) IN(1,4,7,10) THEN 1
WHEN GetMonth(a.Date) IN(2,5,8,11) THEN 2
WHEN GetMonth(a.Date) IN(3,6,9,12) THEN 3
END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1
WHEN GetMonth(now()) IN(2,5,8,11) THEN 2
WHEN GetMonth(now()) IN(3,6,9,12) THEN 3
END AS QTDMonthIndNow

from tableA as a

union

select distinct date,
CASE WHEN GetQuarter(b.Date) = 1 THEN CreateDate(getyear(b.Date),1,1)
WHEN GetQuarter(b.Date) = 2 THEN CreateDate(getyear(b.Date),4,1)
WHEN GetQuarter(b.Date) = 3 THEN CreateDate(getyear(b.Date),7,1)
WHEN GetQuarter(b.Date) = 4 THEN CreateDate(getyear(b.Date),10,1)
END AS FirstDayofQ,

CASE WHEN GetMonth(b.Date) IN(1,4,7,10) THEN 1
WHEN GetMonth(b.Date) IN(2,5,8,11) THEN 2
WHEN GetMonth(b.Date) IN(3,6,9,12) THEN 3
END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1
WHEN GetMonth(now()) IN(2,5,8,11) THEN 2
WHEN GetMonth(now()) IN(3,6,9,12) THEN 3
END AS QTDMonthIndNow

from tableB as b ) as x

If you are using the date dimension file and you wish to write a custom query on top of it, you can use the following code:

SELECT *
CASE WHEN 
(x.QTDMonthInd < x.QTDMonthIndNow and x.Date >= x.FirstDayofQ) or 
(x.QTDMonthInd = x.QTDMonthIndNow AND getday(x.Date)<=getday(now())) 
THEN 1 ELSE 0 END AS isQTD 
FROM
(
SELECT *,
CASE WHEN GetQuarter(d.Date) = 1 THEN CreateDate(getyear(d.Date),1,1)
WHEN GetQuarter(d.Date) = 2 THEN CreateDate(getyear(d.Date),4,1)
WHEN GetQuarter(d.Date) = 3 THEN CreateDate(getyear(d.Date),7,1)
WHEN GetQuarter(d.Date) = 4 THEN CreateDate(getyear(d.Date),10,1)
END AS FirstDayofQ,

CASE WHEN GetMonth(d.Date) IN(1,4,7,10) THEN 1
WHEN GetMonth(d.Date) IN(2,5,8,11) THEN 2
WHEN GetMonth(d.Date) IN(3,6,9,12) THEN 3
END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1
WHEN GetMonth(now()) IN(2,5,8,11) THEN 2
WHEN GetMonth(now()) IN(3,6,9,12) THEN 3
END AS QTDMonthIndNow
FROM Dim_date_CSV d
) AS x

 In this case, replace the inner * with the list of the fields you actually need from the date dimension file.