8 Replies Latest reply on Feb 4, 2019 11:13 AM by Ken Gray

    Vertical Table Calculation Arrangement

    Ken Gray

      I need to create a table that has metrics as rows and brands as columns.  Inside the columns for each brand are two sub-columns for ‘this year’ and ‘last year’.  The problem I’m having is that I need to get a column of ‘% change’ to sit next to the metric in question and show the increase/decrease for just that brand and that metric.

       

       

      del.png

        • 1. Re: Vertical Table Calculation Arrangement
          Ken Flerlage

          In order to properly demonstrate how to do this, we're probably going to need to see a sample workbook so we can understand how the data is structured, but let me try to show you how to do this with SuperStore. The key is that you'll need separate measures for each of the values being displayed.

           

          I'll start by creating a parameter called Current Year which will allow me to change my current year.

           

          Next I'll create calculated fields for This Year, Last Year, and % Change.

           

          TY

          // Sales for current year.

          IF YEAR([Order Date])=[Current Year] THEN

              [Sales]

          END

           

          LY

          // Sales for previous year.

          IF YEAR([Order Date])=[Current Year]-1 THEN

              [Sales]

          END

           

          % Change

          // % change from last year to this year.

          (SUM([TY])-SUM([LY]))/SUM([LY])

           

          Now we'll add a filter on Measure Names and only select the three above. Then we'll put Category and Measure Names on the columns shelf and Segment on the rows shelf. Finally, we'll put Measure Values on the text card. In the end, you'll have something similar to what you're looking for. See attached workbook.

          • 2. Re: Vertical Table Calculation Arrangement
            Ken Gray

            Thanks so much for getting back with me on this.

            Here is as screenshot of the attached workbook.  Unless I'm missing something, I don't think I can manipulate this into the solution I need.  The rows should contain all of the metrics and each column should have ly/ty/yoy variance under each of the brands.  I can't seem to get that metric below to move up into the columns.  If I understand it correctly, your solution had just one of the metrics.  I may be missing the obvious, but I'm not seeing how I can make calculated columns for all the metrics and get them up to the top in 'ly'/'ty'/'yoy' format.  I there a way with the layout below and attached (in version 10.4)?

             

            Thanks.

             

            • 3. Re: Vertical Table Calculation Arrangement
              Ken Flerlage

              Got it. One of the big problems is the structure of your data. You'll be much better off if each metric were pivoted from columns to rows. See the following blog on how to do that: https://www.kenflerlage.com/2018/06/pivoting.html

               

              Once you do that, you'll have a data structure like this:

               

              But we'll still need to create a couple of calculated measures. So create the following calculated fields:

               

              LY

              // Previous value for the brand and metric.

              {FIXED [Brand Code], [Metric]: SUM(IIF([Time Frame]='ly', [Value], NULL))}

               

              TY

              // Previous value for the brand and metric.

              {FIXED [Brand Code], [Metric]: SUM(IIF([Time Frame]='ty', [Value], NULL))}

               

              Change

              // Change from last year to this year

              ([TY]-[LY])/[LY]

               

              Note: I'm using FIXED LOD calculations here. For more information about these, see FIXED Level of Detail Expressions - Tableau. And, with LODs, you have to be wary of the Tableau Order of Operations, especially when you add filters, so you'll want to review this: Tableau's Order of Operations - Tableau

               

              You can then use these to build out your view in the following manner:

              See attached workbook.

              • 4. Re: Vertical Table Calculation Arrangement
                Ken Gray

                That is an awesome solution to this.  Unfortunately, it is based on a 'real' database      Double unfortunately, it's based on a real database that doesn't yet have the pivot function (Impala)

                Thanks for all your work on this though; much appreciated!

                • 5. Re: Vertical Table Calculation Arrangement
                  Ken Flerlage

                  How about using the Tableau Prep method for pivoting? That should allow you to pivot before bringing into Tableau Desktop.

                  • 6. Re: Vertical Table Calculation Arrangement
                    Ken Gray

                    Turns out the database environment we are using is Impala and it doesn't yet support the standard 'pivot' function

                    • 7. Re: Vertical Table Calculation Arrangement
                      Ken Flerlage

                      Yep, and Tableau Prep doesn't yet support Impala as a data source.

                       

                      You could do some tricks with custom SQL, but PIVOT isn't an option. Could try this UNION approach: Transpose columns to rows - Cloudera Community

                      • 8. Re: Vertical Table Calculation Arrangement
                        Ken Gray

                        Here's the process I'm generally using.  This gives me the ability to put the column called 'metric' into the Rows and the column 'value' into the columns as per the calculated columns code above as the measures.

                        Works just dandy if a bit heavy on the SQL side.

                         

                        Thanks for all your help.

                         

                        --  pseudo-code

                        with

                        Q1 AS (

                        SELECT brand, 'TY' AS timeframe, sum(metric1), sum(metric2), sum(metric3), sum(metric4)

                        FROM Table1

                        WHERE date >= today-1year

                        group by brand

                        ),

                         

                         

                        Q2 AS (

                        SELECT brand, 'LY' AS timeframe, sum(metric1), sum(metric2), sum(metric3), sum(metric4)

                        FROM Table1

                        WHERE date < today-1year and date >= today-2years

                        group by brand

                        ),

                         

                         

                        Q3 AS (

                        SELECT * FROM Q1

                        union all

                        SELECT * FROM Q2

                        )

                         

                         

                        pivot_Q4 AS (

                        SELECT 'Metric - 1' AS 'metric', brand, metric1 AS 'value'

                        FROM Q3

                        union all

                        SELECT 'Metric - 2' AS 'metric', brand, metric2 AS 'value'

                        FROM Q3

                        union all

                        SELECT 'Metric - 3' AS 'metric', brand, metric3 AS 'value'

                        FROM Q3

                        union all

                        SELECT 'Metric - 4' AS 'metric', brand, metric4 AS 'value'

                        FROM Q3