7 Replies Latest reply on Aug 5, 2019 3:55 PM by John Sarantos

# Creating a visual based on date filters

Hi,

I am trying to create the following visual which gives the user the ability to compare two quarters or two years. I made a mock-up below. The aim is that the dashboard user can select a start period and comparison period, and the visual will update to show the percent difference of two metrics: average price and revenue. Each dot represents a client. The x axis is the % change in revenue and the y axis is the % change in average equalized rate period over period.

% difference average price = % difference in average(Equivalized Price 30 Seconds) between the start period and comparison period

% difference revenue = % difference in sum(price) between the start period and comparison period

If the user selects "Compare by" quarter, the metrics above calculate by the quarters selected. If the user selects year, the metrics calculate the same metrics for the total year.

I have also attached sample data. It would be great to get some help on how to build this. • ###### 1. Re: Creating a visual based on date filters

Hi Jen,

I included a packaged workbook with two views.  I used parameters to get there.

Step one: Create a Start Parameter

Right-Click on Date -> Create-> Parameter

Set up the parameter as seen here: Step Two: Create a Comparison Parameter

Exact same process, just label it as Comparison Parameter.

Step Three: Right-Click-> Add to Sheet for each one of those

Step Four:  Create Calculated field Percent Difference Average Price

Percent Difference Average Price

(AVG(if datetrunc('quarter',[Date])=datetrunc('quarter',[Start Period]) then [Equivalized Price 30 Seconds] END)

-

AVG(if datetrunc('quarter',[Date])=datetrunc('quarter',[Comparsion Period]) then [Equivalized Price 30 Seconds] END))

/

AVG(if datetrunc('quarter',[Date])=datetrunc('quarter',[Start Period]) then [Equivalized Price 30 Seconds] END)

Step Five: Create Calculated field Percent Difference Revenue

Percent Difference Revenue

(SUM(if datetrunc('quarter',[Date])=datetrunc('quarter',[Start Period]) then [Price] END)

-

SUM(if datetrunc('quarter',[Date])=datetrunc('quarter',[Comparsion Period]) then [Price] END))

/

SUM(if datetrunc('quarter',[Date])=datetrunc('quarter',[Start Period]) then [Price] END)

Step Six: Drag Percent Difference Revenue to Columns

Step Seven: Drag Percent Difference Average Price to Rows

Step Eight: Drag Client to Color

Step Nine:  Click Shape in the Marks Card and click on the Filled Circle

Step Ten:  Drag Client to Text

If you leave it like that, you have your Automatic Axis version.

To set a Fixed Axis:

Step One: Right Click on the Percent Difference Axis->Edit Axis

Step Two:  Change to Fixed and set as you see here (note: You can pick a different range, I picked that based on what I saw happening with 2017 Q2 vs 2018 Q3) Step Three:  Do the same thing to the Percent Difference Average Price, but with a different Range (again based on observation) Thank you,

John

2 of 2 people found this helpful
• ###### 2. Re: Creating a visual based on date filters

This worked and was extremely helpful- thank you so much!

• ###### 3. Re: Creating a visual based on date filters

You're welcome!

• ###### 4. Re: Creating a visual based on date filters

Well Done!!

• ###### 5. Re: Creating a visual based on date filters

Thank you!

• ###### 6. Re: Creating a visual based on date filters

I have one more question!

How do I get the % difference in average price and revenue measures to calculate as 100% if there no value in the start or comparison period?

For example, let's say 2018 Q4 is the Start Period and 2019 Q1 is the Comparison Period. If the client had no purchases in 2018 Q4 (null), but had activity in 2019 Q1 (\$250), how do I have my visual reflect a 100% increase in both % difference average price and % difference revenue? I would want the reverse to also work (2018 Q4 = \$250, 2019 Q1 = null, -100% difference in average price and revenue measures)

Thank you!

• ###### 7. Re: Creating a visual based on date filters

Hi Jen!

Your best bet is to make sure the data you are working with has a value for Price, etc. for each Date period you are looking at, so instead of nulls your underlying data already has zeroes for a date with no sales, etc.  If you data isn't already set up that way, you will need to either change it so that it is before it comes into tableau OR create a date scaffold.

If you head down the date scaffold path, this post links to several more explaining the process.