1 Reply Latest reply on Sep 25, 2013 4:59 PM by Pedro Machado

    Breaking out year-over-year solution by a dimension

    Allison Brown

      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
          Pedro Machado

          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