7 Replies Latest reply on Mar 29, 2013 11:34 AM by Tom Barnes

    Add a percent increase to a YTD Sales

    Tom Barnes

      Recently I needed help building a YTD Sales field that would dynamically change when the data was updated with more information.  Jim Wahl graciously answered that question for me and built a great tool.  However, I am still struggling with the next step which would be to show a % Inc from one year to the next.

       

      So in the test data attached I show the three years worth of data and I need to show a % Inc field between 2012 and 2013.  I am sure this is very simple for someone who knows Tableau but I obviously do not know enough about Tableau to make this work.

       

      Any help would be much appreciated!!!

        • 1. Re: Add a percent increase to a YTD Sales
          Jim Wahl

          Hi Tom!

           

          I couldn't get the table calculation to work last night either, but I think you can also solve this problem with custom SQL that adds the max date to each row. Then it's a simple formula to include only Sales where the week of the current date is <= week of the max date. And another simple formula to calculate % difference.

           

          First, the difficult part -- custom SQL. Normally this would be an easy cross join, but you're working with Excel, which uses Microsoft's JET engine, which does not support a cross join. So it's a little more difficult. James Wright has a nice writeup of the issue here: http://www.interworks.com/blogs/jwright/2013/02/22/musings-tableau-custom-sql-connections-through-jet-driver

           

          Right-click on your data source > Duplicate.

          Right-click on the duplicate data source > Edit Connection and click on Custom SQL.

          Replace the current SQL with

          SELECT [Sheet1].[Date],

            [Sheet1].[ItemDesc],

            [Sheet1].[NewItem],

            [Sheet1].[Qty],

            [Sheet1].[Sales],

            [Summary].[Date Max]

          FROM

            (SELECT [Sheet1$].[Date] AS [Date],

               [Sheet1$].[ItemDesc] AS [ItemDesc],

               [Sheet1$].[NewItem] AS [NewItem],

               [Sheet1$].[Qty] AS [Qty],

               [Sheet1$].[Sales] AS [Sales],

               1 As [One]

            FROM [Sheet1$]

            ) [Sheet1]

            LEFT JOIN

            (SELECT

               MAX([Sheet1$].[Date]) AS [Date Max],

               1 as [One_]

            FROM [Sheet1$]

            ) [Summary]

            ON [Sheet1].[One] = [Summary].[One_]

           

          James's blog post describes the SQL logic. But the goal is to add MAX([Sheet1$].[Date]) to each row.

           

          Click OK. Now you should have two data sources. We'll only use the copy, however, and in the future, you could just use the custom SQL on your main data source so that you have only one.

           

          For now, select the duplicate / customSQL data source and right-click Sales > create calculated field:

          YTD Sales 2 =

          IF INT(RIGHT(STR([Date]), 2)) <= INT(RIGHT(STR([Date Max]), 2))

          THEN [Sales]

          END

           

          I think this is similar to your original approach for summing YTD sales over years with different number of weeks.

           

          Of course, now you can add Date Year to columns and YTD Sales 2 to the text shelf.

           

          Adding % Change is now a simple / quick table calc. Right click on the SUM(YTD Sales 2) pill > Quick Table Calc > % Difference.

          You can now double-click on YTD Sales 2 in the measures pane to get:

          ytd2.png

           

          With this approach, you can also delete the table calcs YTD Sales and Week Max from the previous approach. ...

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Add a percent increase to a YTD Sales
            Jonathan Drummey

            @Jim - James was incorrect in that blog post (I added a comment to that post), JET does support cross joins. The syntax is funky, though:

             

            SELECT [a].[fielda],

              [b].[fieldb]

            FROM [a], [b]

             

            In a case like this, the syntax would be:

             

            SELECT [Sheet1$].[Date] AS [Date],

              [Sheet1$].[ItemDesc] AS [ItemDesc],

              [Sheet1$].[NewItem] AS [NewItem],

              [Sheet1$].[Qty] AS [Qty],

              [Sheet1$].[Sales] AS [Sales],

              [Max].[MaxDate]

            FROM [Sheet1$], (SELECT MAX([Sheet1$].[Date]) AS [MaxDate] FROM [Sheet1$]) AS [Max]

             

            You can see that in the Custom SQL data source in the attached workbook.

             

            @Tom - I've attached a table calc solution. It's a basic % diff from prior calc on YTD Sales, the key is in the Compute Using settings. The Compute Using is on Date Year, Date (in that order) with At the Level set to Year. This way it's addressing on the entire table and only picking the first value from each new Year, which corresponds to the value set by the YTD Sales.

             

            Also, I noticed that the YTD Sales had a Compute Using of Date Year, Date with Restarting Every set to Date Year. This is the same as setting the Compute Using to Date, so I used that because it's simpler to keep track of.

             

            Finally, in Tableau the new v8 layout engine is on by default, it stacks marks and that is why the worksheet has so much white space. A solution for this is to use Analysis->Stack Marks->Off, which reverts to the v7 layout engine and a table that looks better.

             

            In any case, for situations like this I tend to prefer the Custom SQL route, because it lets us build your view using regular aggregates and the simplest % diff calc. Once we start using table calculations, every calculation based on a table calculation has to be a table calc itself and that can drastically increase the complexity of the workbook (for example, by triggering mark stacking and requiring advanced compute using settings).

             

            Hope this helps!

             

            Jonathan

            • 3. Re: Add a percent increase to a YTD Sales
              Jim Wahl

              Cool!

               

              Thanks for the guidance Jonathan. It's very helpful. ...

               

              Jim

              • 4. Re: Add a percent increase to a YTD Sales
                Tom Barnes

                You two are awesome.  I really appreciate the help.  I love the custom sql idea and I think this will work the best in what I am trying to do.  However, the test data I posted was a simplified version of the data which may make this not possible to do, I don't know.

                 

                But I am actually grabbing data from three different sheets which adds to the complexity of the custom sql and I'm not able to get the syntax just right.  I am sure it is all in my FROM clause.  Please let me know if this will still work.  SQL code below.

                 

                SELECT [Data$].[ItemDesc] AS [ItemDesc],

                  [Data$].[Item] AS [Item],

                  [Data$].[Qty] AS [Qty],

                  [Data$].[Sales] AS [Sales],

                  [Data$].[Week] AS [Week],

                  [ItemList$].[Commodity] AS [Commodity],

                  [ItemList$].[Pack] AS [Pack],

                  [ItemList$].[Variety] AS [Variety],

                  [ItemList$].[Weight] AS [Weight],

                  [Week$].[Week] AS [Week$_Week],

                  [Week$].[Date] AS [Date],

                  [Max].[MaxDate]

                FROM ( [Data$]

                  LEFT JOIN [ItemList$] ON [Data$].[Item Nbr] = [ItemList$].[Item No] )

                  LEFT JOIN [Week$] ON [Data$].[Week] = [Week$].[Week]

                  LEFT JOIN (SELECT MAX([Data$].[Week]) AS [MaxDate] FROM [Data$] AS [Max]) ON [Data$].[Week] = [Max].[MaxDate]

                 

                So what do you think?  Where's my error?  I can't give you an attachment without spending a ton of time making up fake data, but if that is the only way to get this answered let me know and I'll start working on it.

                • 5. Re: Add a percent increase to a YTD Sales
                  Tom Barnes

                  So I figured it out.  It was all about the parentheses!  So I did the following and it works great!

                   

                  SELECT [Data$].[ItemDesc] AS [ItemDesc],

                    [Data$].[Item] AS [Item],

                    [Data$].[Qty] AS [Qty],

                    [Data$].[Sales] AS [Sales],

                    [Data$].[Week] AS [Week],

                    [ItemList$].[Commodity] AS [Commodity],

                    [ItemList$].[Pack] AS [Pack],

                    [ItemList$].[Variety] AS [Variety],

                    [ItemList$].[Weight] AS [Weight],

                    [Week$].[Week] AS [Week$_Week],

                    [Week$].[Date] AS [Date],

                    [Max].[MaxDate]

                  FROM ( ( [Data$]

                    LEFT JOIN [ItemList$] ON [Data$].[Item Nbr] = [ItemList$].[Item No] )

                    LEFT JOIN [Week$] ON [Data$].[Week] = [Week$].[Week] )

                    LEFT JOIN (SELECT MAX([Data$].[Week]) AS [MaxDate] FROM [Data$]) AS [Max] ON [Data$].[Week] = [Max].[MaxDate]

                   

                  Thank you both for your help.  Truly appreciated.

                  • 6. Re: Add a percent increase to a YTD Sales
                    Jonathan Drummey

                    @Tom - I'm glad you figured it out!

                     

                    @Jim - You're welcome!

                     

                    Jonathan

                    • 7. Re: Add a percent increase to a YTD Sales
                      Tom Barnes

                      Just another note on this - just in case anyone else ever looks at this thread for advice on how to do this.  I still had a problem with the custom sql in how I had written it.  The "MaxWeek" field was only returning a number when it equaled the Week field, so when I tried to look at YTD I couldn't because only values for that particular week was returned.  So I had to change the final LEFT JOIN clause to include an OR statement in order to get the MaxWeek to list itself on every row.

                       

                      LEFT JOIN (SELECT MAX([Data$].[Week]) AS [MaxWeek] FROM [Data$]) AS [Max] ON [Data$].[Week] = [Max].[MaxWeek] OR [Data$].[Week] <> [Max].[MaxWeek]

                       

                      This allowed the Created Field of YTD Sales to work properly.