Stuff about Data, Tableau and Visualisation
#WorkoutWednesday 2024-01 – Leap Years

#WorkoutWednesday 2024-01 – Leap Years

This is my intended solution for the first #WorkoutWednesday challenge of 2024.


There are only a few basic rules for leap years:

  1. Every year that is divisible by is a leap year (2024, 2020, 2016, etc)
  2. Except every full century, those are divisible by 4 but not leap years (1900, 1800, 1700, etc.)
  3. Except  for centuries divisible by 400, those are leap years (2000, 1600, 1200, etc.)

Most programming languages have a modulo function, which calculates the remainder of a division. Typically the % operator is used and CRMA doesn’t make an exception:

ie 20 % 6 = 2

18 / 6 results in the largest integer (3) with a remainder of 2.

We can use this operator with a few conditional checks to identify a leap year.


After loading the data into CRMA and creating a dashboard, we can add a chart and change the view to a compare table.

In there we can create the following caluclation:

string_to_number(Year) % 4
when 0 then
  case string_to_number(Year) % 100  
  when 0 then 
       case string_to_number(Year) % 400  
          when 0 then "Leap year"
          else "Not a Leap year"
  else "Leap year"
else "Not a Leap year"

There are a few different ways how you can structure the case statement, this is the one that made most sense to me.

Once this is calculated, you can create a stacked column chart and use the newly created field for the conditional formatting. Then it’s just a matter of adjusting some of the display settings to add a title and remove the y axis and the bar labels.

Different approach

Roman Michalik came up with a different approach here:

Rather than using the modulo operator, he used day_of_year() by creating a date for the 31st of December of each year and checking which day it is. If the result is 365 it is not a leap year, if the result is 366, it is a leap year. Other than personal preference, I cannot really see an advantage to do it either way.

One comment

  1. Roman Michalik

    In my solution I was using an even simpler formula:

    day_in_year(toDate(Year + “-12-31″,”yyyy-MM-dd”)) -365

    With that, you get a 0 for non leap years and a 1 for leap years.

    From my perspective, the main difference between the two approaches is that with the day_of_year() you don’t have to know the logic of leap years (with all its details) and let CRM Analytics do the job, instead of defining all the cases when a year is a leap year or not on your own.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.