cancel
Showing results for
Did you mean:

# Calculate QTD (Quarter to date) flag

Community Team Member
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.
Version history
Last update:
‎03-02-2023 08:29 AM
Updated by:
Contributors
Community Toolbox

Developers Group:

Product Feedback Forum: