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

## Concept

There are only a few basic rules for leap years:

- Every year that is divisible by is a leap year (2024, 2020, 2016, etc)
- Except every full century, those are divisible by 4 but
**not**leap years (1900, 1800, 1700, etc.) - 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.

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.