# Breaking out year-over-year solution by a dimension

We recently created a nice year-over-year solution to compare specific dates in our client's fiscal calendar (see tab: Daily YoY in attached workbook), however, I cannot figure out how to make this break out by a dimension (in this case, "source").

Methodology for Daily YoY:

Data Sources:

• Calendar (primary) - primary data source

This contains our client's fiscal calendar with "Date" and "Year Ago Date" aligned

• By Source (this year) - secondary data source

This contains all of our metrics by day and links "Trend Range" to "Date" in Calendar

• By Source (last year) - secondary data source

This is a duplicate of the previous source, but links "Trend Range" to "Year Ago Date" in Calendar

Parameters:

• Date Option - Boolean
• Starting & Ending Date - Date

Filters:

Date Option - calculated field that either takes yesterday's data or data from between the parameter start and end date based on this formula:

if [Parameters].[Date Option] = TRUE and  TODAY()-1 = [Date] then 1

elseif [Parameters].[Date Option] = FALSE and ([Date] >= [Starting Date] and [Date]<=[Ending Date]) then 1

else 0 end

(thanks to Michel Caissie)

I then created calculated metrics in the primary data source that were aggregates from the other two sources

Visits (this year) - visits from By Source (this year)

Visits (last year) - visits from By Source (last year)

Now...I want to break this out by a dimension. It works fine if I break it out by any of the date dimensions because they are originally in the primary source (Calendar), but I cannot break it out by any of the secondary data source dimensions.

In the second tab (YoY by Source) I have it broken out by traffic source for this year's data and it is adjustable by the parameters, but I cannot figure out how to get last year's data in there.

Any ideas?

• ###### 1. Re: Breaking out year-over-year solution by a dimension

Allison,

An idea would be to create a datasource that joins your calendar datasource with all possible values of [Source]. Essentially, it would have a record for all sources for each day contained in your data.

Then, you should be able to blend the data and break it by [Source].

You can do this via custom SQL. This SQL is not in Tableau syntax, but it should give you an idea of what I mean:

SELECT c.*,

Source

FROM calendar c JOIN ( SELECT DISTINCT Source

FROM  BySource ) s

Perhaps someone else has another idea. Let me know if this works.

Pedro