cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Question

For a specific industry, like Education, even though the number of days is roughly the same every year, each academic year can start and end on different dates. The usual way of calculating YTD wouldn't work for these requirements because the school year can start and end in the middle of the calendar year.

Answer

The idea is we want to compare the years by the numbers of days that have passed since the current year started, e.g. compare the first 135 days of this school year with the first 135 days of last school year, the first 135 days of last two school years, the first 135 days of last three school years, and so on, regardless of the actual dates.

To accomplish this, we need to create a date dimension table with a day rank and a YTD flag. Day rank is an enumeration of school days within each school year. By comparing the day rank of each day by the day rank of today's date, we can calculate the YTD flag.

We need these two tables for this solution to work:

  • List of school year with start and end dates
  • List of dates. You can use the date dimension Excel file from this article.

School Year table:

Community_Admin_0-1634387842116.png

Date table (you only need the Date column for this purpose)

Community_Admin_1-1634387842196.png

Step 1

Create a new dim date table using the custom table option:

  • Get the list of school dates (only dates between start and end dates of each school year) and remove dates that fall on Saturday and Sunday (optional).
  • Rank the dates within each school year.
  • Based on the result from above, get today's date rank.
  • Compare each date's rank with today's rank to calculate the YTD flag.

You can use this SQL as an example:

--Join the list of school dates with today's date rank

--Compare each date's rank with today's date rank to create YTD flag

SELECT *,

CASE WHEN t1.[Day Rank] <= t2.[Today Day Rank] THEN 1

ELSE 0

END AS isYTD

FROM

 (

 --Get the list of only school dates (dates between start and end date)

 --Remove dates that fall on Saturday and Sunday (not school dates)

 --Rank dates within each school year

 SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank]

 FROM [Dim Dates] d

 JOIN [School Year] s

 ON d.Date BETWEEN s.[Start Date] AND s.[End Date]

 WHERE DayOfWeek(d.Date) <= 5

 ) t1

 

JOIN

 

 (

 --Get today's date rank

 SELECT t.[Day Rank] AS [Today Day Rank]

 FROM

  (

  --Get the list of only school dates (dates between start and end dates of each year)

  --Remove dates that fall on Saturday and Sunday (not school dates)

  --Rank dates within each school year

  SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank]

  FROM [Dim Dates] d

  JOIN [School Year] s

  ON d.Date BETWEEN s.[Start Date] AND s.[End Date]

  WHERE DayOfWeek(d.Date) <= 5

  ) t

 WHERE t.Date = createdate(getyear(now()), getmonth(now()), getday(now()))

 ) t2

ON 1=1

The table should look like this:

Community_Admin_2-1634387842199.png

Step 2

Link the fact table(s) to the new date dimension table on the Date field.

Community_Admin_3-1634387842076.png

Step 3

Create your widget(s) and add the YTD flag as a filter, set to 1.

Community_Admin_4-1634387842188.png



Version history
Last update:
‎10-16-2021 05:39 AM
Updated by:
Contributors
Community Toolbox

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

Need additional support?:

Community Support Request