Pulling year over year data in a side by side format

  • 9 April 2015
  • 6 replies

Has anyone found a clean, simple way to chart year over year transactional data, with this year as one line and last year as another line?

Or, in table format:

Date, Revenue This Year, Revenue Last Year

… for 30 days of data

I’d like to be able to only show 30 rows of data, not 395 (365 + 30) rows of data, which is part of the difficulty.

I’ve been able to solve this with a nasty hack, but am looking for cleaner solutions. My solution is below. Would appreciate any improvements!

The overall idea is to adjust the date (in my case, “completed_date”) to a date in the last 365 days. If the date was in the last 365 days, it’s not adjusted at all. Otherwise, it’s adjusted a corresponding amount. The example below is slightly more complicated, because I’m actually adjusting by only 364 days, to maintain comparisons of Mondays to Mondays.

For filtering:

  - dimension: completed_month_day_dow_adj_to_current_year

type: time

timeframes: [date, day_of_week]


sql: |

DATE_ADD(${TABLE}.completed_at, INTERVAL 364 * FLOOR(DATEDIFF(CURDATE(), ${TABLE}.completed_at)/364) DAY)

I also created a separate dimension specifically for the label that adds the day of week, so it’s easier to read the data:

- dimension: completed_month_day_dow_adj_to_current_year_label

type: string

sql: |

DATE_FORMAT(DATE_ADD(${TABLE}.completed_at, INTERVAL 364 * FLOOR(DATEDIFF(CURDATE(), ${TABLE}.completed_at)/364) DAY), '%Y-%m-%d (%a)')

With this dimension in place, I use this new dimension as my main dimension, pivot on completed_year, and add a measure (e.g. revenue). The filters are the key to eliminating all of the extra data between 30 days and 365 days out.

completed_at: in the past 400 days

completed_month_day_dow_adj_to_current_year: in the past 30 days

This combination of filters returns 60 rows of data, that are then pivoted on year, putting the data side by side.

The obvious downside to this approach is that you can only do easy comparisons of periods in the last 365 days. Spanning the 1 year mark doesn’t work. Also, a different hack is needed if you also want to be able to include future dates on your table (for example, if you want to put the next 7 days also on your table to see what to expect). Without a separate hack, future dates are also mapped to the last 365 days, which means tomorrow ends up with a date 364 days ago.

Would love to find a cleaner solution to this problem. Hopefully I’ve missed a very simple one!

6 replies

Userlevel 6

@jyau In 3.18, OR is supported between dates in filters. You can specify two time ranges and pivot them out by year and get I think what you want. Here is an article on it.

Filter on Multiple Date Ranges (OR filters between dates) (3.18+)

Aha, this will be a nice, clean solution to the problem. I think for my more specific scenario of doing day of week matching/adjustment, my hack will still be required, but I think this multiple date filter approach is great for 90% of cases. Thanks!

Userlevel 4

One other way to approach this is to use table calculations. Using table calculations, you can simply create another column that offsets a given date by some given time lag.

Here’s a quick example of one and two week offsets:

I wound up solving a similar problem but in a slightly different way than the others described.

I created two dimensions associated with the view I wanted to compare year over year.

 - dimension: created_year_only

hidden: false

type: int

sql: DATE_PART(year, ${TABLE}.created_date)

- dimension: created_date_only

sql: to_char( ${TABLE}.created_date, 'MM-DD' )

The “date_only” field was necessary because using the “date” part of the native date dimension group includes the year with the date.

Then a simple explore of the measure for the “date_only” field and the “year_only” field as a pivot, gives the following:

I’m not sure why the graph doesn’t cover the whole width of the view, but there is a lot of white space on either side of the actual data.

The table visualization of the data provides the view the original poster described as wanting and filters can be added on the “date_only” field for instance “starts with 05” to get only May data for each year.

I’ve don this a few times, but your link appears to be broken. Could you update it?

Thanks @colin - that offset and explanation really helped in something I was doing quickly 🙂