Showing results for 
Search instead for 
Did you mean: 
Community Team Member
Community Team Member
This article outlines how to create a date table that flags whether or not a particular date is within a particular range, based off of the current date. For example, if today is the 100th day of the year, we may want to analyze information for the first 100 days of the past several years. 
The flags can identify if it is up to the current
  • Day of the week
  • Week of the Year
  • Day of the Month
  • Day of the Quarter
  • Day of the Year
This will be implemented as a table created via a custom SQL expression in the Elasticube. This expression uses a date table, which is described and can be downloaded under this Date Dimension File Article. Additionally, it joins against the data table that is being used for analysis, to only contain the necessary dates.
The following code is used to define the date table
CASE WHEN a.DayNumOfWeek<=t.DayNumOfWeek THEN 'Yes' ELSE 'No' END AS [Is Week to Date?],
CASE WHEN a.WeekNum<=t.WeekNum THEN 'Yes' ELSE 'No' END AS [Is Week of Year to Date?],
CASE WHEN a.DayNumOfMonth<=t.DayNumOfMonth THEN 'Yes' ELSE 'No' END AS [Is Month to Date?],
CASE WHEN a.DayNumOfQuarter<=t.DayNumOfQuarter THEN 'Yes' ELSE 'No' END AS [Is Quarter to Date?],
CASE WHEN a.DayNumOfYear<=t.DayNumOfYear THEN 'Yes' ELSE 'No' END AS [Is Year to Date?]
FROM [Date Dimension.csv] a
--Establish the necessary date range
INNER JOIN (SELECT MIN(s.OrderDate) AS Min_Date, MAX(s.OrderDate) AS Max_Date FROM [SalesOrderHeader] s) mm
ON a.Date BETWEEN mm.Min_Date AND mm.Max_Date
--Find date information for the current day
(SELECT, d.DayNumOfMonth, d.DayNumOfQuarter, d.DayNumOfYear, d.WeekNum, d.DayNumOfWeek
FROM [Date Dimension.csv] d
WHERE d.Date=CreateDate(GetYear(Now()), GetMonth(Now()), GetDay(Now()))) t

Version history
Last update:
‎02-09-2024 11:00 AM
Updated by:
Community Toolbox

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

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: