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

# Site Visits type Quarter over Quarter? Need help

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

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.

[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

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

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