4 Replies Latest reply on Sep 18, 2017 5:28 AM by Analytics analytics

    Realigning Tableau data sets with non standard report periods

    Analytics analytics

      Hi there

       

      So my client's date periods don't follow Tableau standard report weeks / months / Year (I had a f2f session with a Tableau product developer in London last week and she had never come across it and apart from created daily level dimensions and related values I've got nowhere...

       

      My 2 main sources of data are a SQL Database (for Sales / Customer) and Google Analytics (for Web analytics) - dashboards and tables have been created at present using the Tableau standard date dimensions.

      The client's reporting week starts from a Sunday to Saturday and the Periods start at whatever periods they have defined for which I have created a flat file below

       

      This is what the various date fields look :

      My flat file (imported into Tableau)

           

      REPORT_YEARPERIODDATEDAY_OF_WEEKWEEK_NUM
      2016P110/01/2016SUN1
      2016P111/01/2016MON1
      2016P112/01/2016TUE1
      2016P113/01/2016WED1
      2016P114/01/2016THU1
      2016P115/01/2016FRI1
      2016P116/01/2016SAT1
      2016P117/01/2016SUN2
      2016P118/01/2016MON2
      2016P119/01/2016TUE2
      2016P120/01/2016WED2
      2016P121/01/2016THU2

       

      The SQL database Date field is DATE timestamped.

       

      Created At
      26/07/2017 00:00
      28/07/2017 00:00

       

      Google Analytics imports as Automatic (by Day)

       

      Hope someone can help.

      Thanks

      Suki

        • 1. Re: Realigning Tableau data sets with non standard report periods
          Michael Gillespie

          Suki, I'm not sure there's an actual question in your post!  However, I think I know what you're getting at.

           

          You are VERY close to the right answer.  The key is getting your custom calendar file right, then making sure that the DATE fields in your SQL table and GA data are all of the same type.

           

          1) The custom period spreadsheet.  You need to account for every single day in this spreadsheet, so one row per day.  That row should contain the following columns:

           

          Key, Date, Year, Period, WeekNum, Year_Start, Year_End, Period_Start, Period_End, Week_Start, Week_End

           

          You don't need "Day of Week" - you can calculate that in Tableau when necessary.  This format means that every single row contains all the information you need to figure out where in the custom calendar you are relative to the year, period and week.

           

          The "Key" field is what you will use to join this spreadsheet to the SQL and GA data.  It is a copy of the DATE field.

           

          2) Your SQL table is very likely formatted as a DATETIME field, and you won't be able to join it to a plain DATE field.  Create a copy of the field (in Tableau) and format it as DATE (or change the type of the existing column to DATE in Tableau).

           

          3) Get your GA date field to match the spreadsheet format - probably needs another calculated field.

           

          Once those are done, start a join with the custom calendar spreadsheet as the left side of the join, and connect your SQL data and your GA data on the KEY field.

           

          That should get you started!

          • 2. Re: Realigning Tableau data sets with non standard report periods
            Analytics analytics

            Hello Michael

             

            Hope you had a great weekend...

             

            I totally forgot to reply to you on this to say thankyou (!!) - suffice to

            say I've still not tackled (still in process!) it but I understand what you

            are saying - I've not actually joined data is this way - but had to do it

            with Google Analytics data so I think I understand what I need to do......

             

            Thanks

            Suki

             

            On 12 September 2017 at 14:36, Michael Gillespie <

            • 3. Re: Realigning Tableau data sets with non standard report periods
              Michael Gillespie

              Suki, glad to hear this is what you needed.  If it turns out to be the exact answer, please come back to this post and mark it as "Correct Answer" so others can find it and it drops off the "Unanswered Questions" queue.

               

              Happy Monday!

              1 of 1 people found this helpful
              • 4. Re: Realigning Tableau data sets with non standard report periods
                Analytics analytics

                Sure will do

                 

                On 18 September 2017 at 12:54, Michael Gillespie <