4 Replies Latest reply on Jun 23, 2017 11:00 AM by Jana STUPAVSKY

    How to display YTD & YTD Growth only up to the selected month?

    Jana STUPAVSKY

      Hi - I am having troubles displaying YTD and YTD Growth (for calendar year, not fiscal) graphs correctly. I am working with real estate sales data in my attached workbook.

       

      I would like to be able to set my Month filter to Single value, so that I can pick any month of the year and view YTD and YTD Growth up to that month. Right now, I have to check all previous months in the filter in order to get the correct YTD and YTD Growth numbers.

       

      I am sure this is quite simple for experienced Tableau users, however, it seems like I am going in circles trying to solve this by reading various forums on this topic. Can anyone help, please?

        • 1. Re: How to display YTD & YTD Growth only up to the selected month?
          Matt Arnold

          Hi, Jana.

           

          This functionality can be achieved with a parameter and a calculated field. I have worked through this in the attached workbook and the steps below can be followed to practice personally.

           

          + Create a parameter: "!Number Sold Up To:"

          Data type - Date

          Current Value - (unimportant now)

          Display format - Month Year (June 2017)

          Allowable Values - Range, Set from Field > Date (this drops in the Min and Max from your data)

          OK

           

          + Create a calculated field: "! #Sold"

          Syntax - IF [Date] <= [!Number Sold Up To:] THEN [# Sold] END

           

          Building the view: Replace the SUM(#Sold) with SUM(! #Sold) on the Columns shelf (builds the bars) and the Label button of the Marks Card (labels the bars). Lastly, right-click the parameter and select > Show Parameter control.

           

          Hope that helps!

          • 2. Re: How to display YTD & YTD Growth only up to the selected month?
            Jana STUPAVSKY

            Matt - thank you for your reply. I didn't see you attaching any workbook, but with the steps you described, I think I got what you suggested. It seems to be working great, except it's not applying the calculation to the previous year  (2016), making the previous year bar remain constant. It only applies changes of months to the current year (2017). Any suggestion on that?

             

            I would like, by selecting a month, both years - current and previous, show their YTD calculations.

            • 3. Re: How to display YTD & YTD Growth only up to the selected month?
              Matt Arnold

              Jana,

               

              We can make a few changes to how the parameter and calculation are constructed to achieve the YTD sum of sold. See below and the attached workbook for the changes. As for the attached workbook not showing up, it should be available just below the text of the post.

               

              *If anyone else opens this thread, would you please comment if the attachments are showing? Thanks!

               

              + Create a parameter: "!Number Sold Up To:"

              Data type - Date

              Current Value - 1/31/2016

              Display format - bottom option, Custom - in the syntax box, enter - mmmm     

              (This put the full name of the month for the parameter selector)

              Allowable Values - Range, Min - 1/31/2016, Max - 12/21/2016, Step size - monthly

              OK

               

              + Create a calculated field: "! #Sold"

              Syntax - IF MONTH( [Date] ) <= MONTH( [!Number Sold Up To:] ) THEN [# Sold] END

               

              Best of luck!

              2 of 2 people found this helpful
              • 4. Re: How to display YTD & YTD Growth only up to the selected month?
                Jana STUPAVSKY

                Matt - thank you. This helped. How would I apply the same to my YTD Growth viz? I only want to show the YTD Growth for the month I am currently selecting; no previous months.