7 Replies Latest reply on Dec 7, 2018 1:36 AM by Ciara Brennan

    YTD & Prior YTD with specific measures

    john Doe

      Hi,

       

      I hope you all could help me with a solution, I couldn't find it anywhere in this community.

      I won't be able to add the workbook here due to confidential information.

       

      I want a dashboard similar to below image. Data is up to Nov 2018, and I want to compare it to the same period for prior year.

       

      YTDPrior Year
      Sales1,000,000(123,000)
      #Transaction5,000125

       

      I got YTD filter to work with this formula [Date]<=Today()AND DATETRUNC("Year", [Date]=DATETRUNC("Year",Today()) but can't figure how to do PY.

      I would like to have Prior Year to show the difference between YTD & PY instead of the actual number.

       

       

       

       

       

       

      Data Source:

      Online Sales

      Store Sales

      I created measures with Sales as Sum(Online Sales) + SUM(store Sales)

       

      Date format is mmm-yy

        • 1. Re: YTD & Prior YTD with specific measures
          Jennifer VonHagel

          Hi,

           

          I'd create a Date Range dimension that groups Current Year YTD dates, Previous Year YTD dates, and excludes all others:

          IF [Order Date] <= Today() AND YEAR([Order Date]) = YEAR(TODAY())

          THEN 'Current YTD'

          ELSEIF [Order Date] <= DATEADD('year',-1,TODAY()) AND YEAR([Order Date]) = YEAR(TODAY())-1

          THEN 'Previous YTD'

          ELSE 'Exclude'

          END

           

          You can put this on the filter and exclude the "Exclude" Dates, then show values for a couple measures:

           

          Best,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: YTD & Prior YTD with specific measures
            john Doe

            Thank you Jennifer! I think this could work for what i need.

            • 3. Re: YTD & Prior YTD with specific measures
              john Doe

              One more question. How could I subtract Current YTD to Previous YTD with this formula?

              • 4. Re: YTD & Prior YTD with specific measures
                Jennifer VonHagel

                Hi,

                 

                To set up your table in the format you have it (Metrics as rows, two date periods along with a Difference and % Difference as columns) is not easy to do in Tableau (please, anyone else is the forum jump in and let us know if you know an easy way ).

                 

                One thing you could do is create separate measures for each Metric, Date Range, Difference (and I'm throwing % Difference in as well because most people want that).  You can see that these are all calculated fields.  You'd need to create 4 calculated field per metric you want to show.  The table format will just be a straight line of metrics, rather than Rows of Metrics and Columns of Date Ranges with Dif and % Dif.

                 

                Sales C-YTD: IF [Date Range] = 'Current YTD' THEN [Sales] ELSE 0 END

                Sales P-YTD: IF [Date Range] = 'Previous YTD' THEN [Sales] ELSE 0 END

                Sales Difference: SUM([Sales C-YTD]) - SUM([Sales P-YTD])

                Sales % Difference: ( SUM([Sales C-YTD])/SUM([Sales P-YTD]) ) - 1

                 

                You could force these metrics into a table-like format, but it's all extremely manual:

                 

                I've set up an Axis for each column: Row Headers (metric names), Current YTD, Prior YTD, Difference, % Difference.  The Axis is a calculated field which is simply ATTR(1).

                 

                When you put this on the columns shelf, set its Marks Card to "Text" rather than "Automatic", right click the Axis and un-check Show Header to get rid of the Axis label and tick marks underneath.

                 

                For Axis 1 I created a Row Headers Calculation which is the names of my metrics separated by New Line (char(10)).  Drag this onto Axis1's Text button.

                 

                Go into the Text Button and format the text (I left aligned it, and added to lines above so it will line up with the numbers we put in:

                 

                For Axis 2, I put in the "Current YTD" header, a blank line, then the CYTD Sales and CYTD Quantity. I left aligned them:

                 

                Rinse and repeat for the other three axes.

                 

                There are probably at least two other ways to get your data into this kind of table format that involve data restructuring.  After the data restructuring and creation of calculations the setup may be less manual than above, but I don't have time to work them out right now, if interested I can write them out later:

                1. Pivot your metrics so Metric Name is a dimension and Metric Value is a single column for all measures. The challenge with this is applying different formats in the same view ($, %, decimals, etc).

                2. Maybe if we add records to the data which will be null but placeholders for Difference and % Difference, we could fill these with the appropriate calculations once in Tableau. Not sure about this, I'd have to work it out. We would need the two additional records to be repeated for every combination of dimensions in the data, and probably would have a nonsense date so we could recognize them (like 1/1/1000 and and 1/2/1000). Then we could create the Date Range as above with Current YTD, Previous YTD, Exclude, and the two new records would become Difference and % Difference into which I think we could use Lookup to get the right string of Metrics depending on Date Range field.

                 

                Hope this helps .

                 

                Best,

                Jennifer

                • 5. Re: YTD & Prior YTD with specific measures
                  Ciara Brennan

                  Hi John, just checking your query is now resolved?

                   

                  Thanks, Ciara

                  • 7. Re: YTD & Prior YTD with specific measures
                    Ciara Brennan

                    Glad to hear it, Joe