#WorkoutWednesday 2024-01 – Leap Years

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

Concept

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: https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_operators_arithmetic.htm

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.

Solution

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:

case
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"
      end
  else "Leap year"
  end
else "Not a Leap year"
end

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: https://www.linkedin.com/feed/update/urn:li:activity:7148388015252410368/

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.