cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
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

Recommended quick links to assist you in optimizing your community experience:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

Sisense Privacy Policy