3 Replies Latest reply on Jul 15, 2016 12:38 PM by Shinichiro Murakami

    Site Visits type Quarter over Quarter? Need help

    Jimmy Barber

      Hey Guys, first off thanks for the help, I feel like this might be a tricky one?

       

      Attached is my workbook. I have the following fields:

       

      Color: Main color of each website

      Site Number: reference number to a specific page

      Quarter: Quarter of the year

      Active: Was the site active at the time?

      Visits: total page visits

       

      Goal: Determine how many visits are on a new color/site, as well as how many visits are on a comp (active q1 2015 and q1 2016) site?

       

      The formula would look like this:

       

      If active in Q12015 is 0 and Q12016 is 1 then "New Color"

      If active in Q12015 is 1 and Q12016 is 1 then "Comp Color Visit"

      If active in Q12015 is 1 and Q12016 is 0 then "Deleted Color"

       

      In the end i'm wanting to chart visits by quarter (x axis) and show what amount of visits (y axis) were from "New Colors", from "Comp Colors", and from "Deleted Colors"?

       

      I know all things are possible in Tableau, trying to believe I can get this to work!

       

      THANK YOU

        • 1. Re: Site Visits type Quarter over Quarter? Need help
          Shinichiro Murakami

          There are two "Active" status on same quarter x same color x same site number combination.

          Then there are four potential different statuses in above combination.

           

          Anyway, you can pick one of those.

           

          The formula concept is as below. 

          Overview: Level of Detail Expressions

           

          [Status  max]

          if {fixed [Site Number],[Color]:MAX(if [Quarter]="Q12015" then [Active] end)}= 0

          and

          {fixed [Site Number],[Color]:MAX(if [Quarter]="Q12016" then [Active] end)}= 1

          then "New Color"

          elseif

          {fixed [Site Number],[Color]:max(if [Quarter]="Q12015" then [Active] end)}= 1

          and

          {fixed [Site Number],[Color]:max(if [Quarter]="Q12016" then [Active] end)}= 1

          then "Comp Color Visit"

          elseif

          {fixed [Site Number],[Color]:max(if [Quarter]="Q12015" then [Active] end)}= 1

          and

          {fixed [Site Number],[Color]:max(if [Quarter]="Q12016" then [Active] end)}= 0

          then "Deleted Color"

          ELSE

          "ERR"

          END

           

           

          [Status min]

          if {fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 0

          and

          {fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 1

          then "New Color"

          elseif

          {fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 1

          and

          {fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 1

          then "Comp Color Visit"

          elseif

          {fixed [Site Number],[Color]:min(if [Quarter]="Q12015" then [Active] end)}= 1

          and

          {fixed [Site Number],[Color]:min(if [Quarter]="Q12016" then [Active] end)}= 0

          then "Deleted Color"

          ELSE

          "ERR"

          END

           

           

          Thanks,

          Shin

           

          9.3 attached.

          • 2. Re: Site Visits type Quarter over Quarter? Need help
            Jimmy Barber

            This is awesome Shin!

             

            Follow up question: if i were to calculate this by week instead of quarter, would you know a more efficient way other than listing out (like below) every week for all 52 weeks?

            if {fixed [Site Number],[Color]:min(if [Week]="012015" then [Active] end)}= 0

            and

            {fixed [Site Number],[Color]:min(if [Quarter]="012016" then [Active] end)}= 1

            then "New Color"

            • 3. Re: Site Visits type Quarter over Quarter? Need help
              Shinichiro Murakami

              If you want to show the newest week always, below formula can be dynamic.

               

              if {fixed [Site Number],[Color]:min(if [Week] [Quarter]={fixed : max( [Week]  [Quarter])} then [Active] end)}= 0

              and

              {fixed [Site Number],[Color]:min(if [Quarter]="012016" then [Active] end)}= 1

              then "New Color" end

              Thanks,

              Shin