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

    Creating a visual based on date filters

    Jen Fortini

      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
          John Sarantos

          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)

           

          Please let me know if this was helpful/answered your questions!

           

          Thank you,

          John

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

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

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

              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
                John Sarantos

                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.

                 

                Data Scaffolding in Tableau