6 Replies Latest reply on Apr 12, 2017 12:56 PM by Joe Mako

    Excel like Tabular view without Measures Concatenating

    Char Herrick

      Hi there,

       

      I've been searching the forums and I can't find exactly what I want (this is close but not 100% what I need: Expanding beyond 16 columns without header concatenation | Tableau Software ;Re: Multiple measure & difference between two columns ; Maximum number of columns displayed? https://www.tableau.com/about/blog/2013/5/sometimes-you-have-break-rules-expanding-beyond-16-columns-without-header) I've already added the max, and I apologize in advance if this has already been covered. I am trying to put an Excel report into Tableau with a data warehouse and Sales force connection. Anyways, I have Dimensions and Measures I need in a tabular view. I have my MY(Month) in Columns and my Region, ID, State in Rows. When I try to add in measures, then the measure appears under each month, I need them to be their own columns like the attached picture. I've also attached a screenshot (minus my customers names) of the view I have so far. You will see from the 'Header' snapshot that after the months I need to add in then Change (variance YOY from the last month present, so in this case Nov-16 - Nov-15) and the % (Growth). Next is notes and Rep I'm pulling from Sales force. But to do this the measures will appear under each month and that wont work. If you could point me in the right direction that would be helpful. I feel like it should be simple and I should know it but I am drawing a blank after staring and googling the past 2 days :/

       

      Headers in Excel: Headers.png

       

      Tableau Workbook: workbook.png

        • 1. Re: Excel like Tabular view without Measures Concatenating

          Hey Char,

           

          Often when those sorts of questions are asked, the underlying issue comes down to trying to use Tableau for the wrong uses. Articles like this help shed some light:

           

          3 Tips to Overcoming the Excel Barrier to Tableau Adoption | Tableau Public

          5 Ways How You Should NOT Use Tableau

           

          Either way, assuming you're committed to making this sort of view in Tableau, a packaged workbook would be the best way for us to take a look and give you a more specific answer. If you have sensitive data, check out Anonymize your Tableau Package Data for Sharing.

           

          Thanks,

           

          -Diego

          • 2. Re: Excel like Tabular view without Measures Concatenating
            Char Herrick

            To be more specific, I know Tableau is nothing like excel,maybe be I should be more clear, what I want is a roll up/to show the % change and variance for the months I set. Those columns can be before the months or after, it really doesn't matter. What matters is the data I have is the same data shown in the excel screenshot. That is why my title is 'Excel like'. I'd like a Tabular view for ease of quick viewing but if there is a different suggestion on how to best show the data without compromising the wanted columns I am open to it. Maybe I need to do something like a cohort analysis by customer showing the months they went down and what the YOY was, idk. But I know there are workarounds as evident in the links I provided.

            • 3. Re: Excel like Tabular view without Measures Concatenating

              My apologies Char, I didn't mean to insinuate that you didn't know how to use Tableau! Often when I see these sorts of questions, they can be the result of an inherent misunderstanding of the product. Either way, let me see if I can get someone to take a closer look for you!

               

              -Diego

              • 4. Re: Excel like Tabular view without Measures Concatenating
                Char Herrick

                No my apologies, I have turned this and a couple other projects inside out. Would be almost easier to keep in excel but we are trying to minimize the manual work. This is to show the drops in volume essentially looking MOM and YOY with the client details. The hard part is combining all into a view that is simple and easy for non-tableau users to understand. That is my struggle.

                 

                Thanks,

                Char

                • 6. Re: Excel like Tabular view without Measures Concatenating
                  Joe Mako

                  Key: In Tableau, if you want a mark, you need a record.

                   

                  Since you want some more marks: "I need them to be their own columns"

                   

                  You will need a record for each mark within each new column you want to display.

                   

                  There are a few ways to get more records in Tableau:

                  - Exists in underlying data that a Tableau worksheet queries, that is generated in the connection or with custom SQL (eg joining/unioning/pivoting). Using a Cartesian join to multiple that data is also useful. Another variation is what I call scaffolding where a primary data source is created with all potential dimension combinations of interest and using data blending to bring in the aggregated data exactly as desired.

                  - Measure Name/Measure Values late pivot, the additional records generated comes late in the pipeline, but is useful for display purposes, and cannot be used to impact computation. All pills that on the Measure Values shelf must be Continuous Measures resulting in a numeric data type.

                  - Totals (Grand Totals and Subtotals) are the reevaluation of the view with different combinations of discrete dimension pills not impacting it. For example a Columns Grand Total is like a duplicate worksheet, but without the impact of the discrete dimension pills from the Rows shelf. Table Calculation pills on the Filter shelf do not impact Totals.

                  - Domain Padding: A type of Data Densification that requires a range aware pill, a pill with a interval set (bin or most kinds of Date/Time configurations), for every possible interval between the min and max value, a mark will be generated before data blending. Cannot pad greater than the max value nor less than the min value in the underlying data.

                  - Domain Completion: Is a massive beast of complexity, A type of data densification that creates a mark for dimension value combinations. If the value does not exist in the underlying data, it cannot be generated.

                   

                  With this in mind, I believe the simplest way to get the results that you want is to duplicate the data with a Cartesian join. A Cartesian join multiples the data. For example if one table has 100 records, and the other 8 records, the result will be 800 records.

                   

                  In your case you will need a five copies of data, one for each set of columns you want.

                   

                  Data Type

                  Month

                  Change

                  Percent

                  Notes

                  Rep

                   

                  Month is for your current data, and the other four copies will allow for four additional columns to be added.

                   

                  1. Here is the custom SQL that I used with a legacy connection ot the attached Excel file:

                   

                  SELECT * FROM [Data$],[Columns$]
                  

                   

                  Here you can see the record duplication:

                  custom sql.png

                   

                  I recommend creating an extract.

                   

                  Also connect to your salesforce data as a separate connection with the Notes and Rep data.

                   

                  2. Place "Data Type" on the Columns shelf and apply a manual sort, and then un-check "Show Header".

                   

                  3. Then create a calculated field for the header:

                   

                  IF [Data Type]="Month" THEN
                    DATENAME('month',[Month of Monthdate])+" "+DATENAME('year',[Month of Monthdate])
                  ELSE [Data Type] END
                  

                   

                  Place "Header" on the Columns shelf, and sort on the field Month, minimum, so the months are in the correct order.

                   

                  sort header.png

                   

                  4. Place your other dimension pills on the Rows shelf

                   

                  5. create calculated fields for your last month and the same month the previous year

                   

                  Last Month

                  ZN(SUM(IF [Month]={MAX([Month])} THEN [Total Vol] END))
                  

                   

                  Previous Year

                  ZN(SUM(IF DATEADD('year',1,[Month])={MAX([Month])} THEN [Total Vol] END))
                  

                   

                  6. Then create a calculated field for each format you want to display:

                   

                  Dollar:

                  CASE ATTR([Data Type])
                  WHEN "Month" THEN SUM([Total Vol])
                  WHEN "Change" THEN [Last Month]-[Previous Year]
                  END
                  

                   

                  Percent (I do not know what logic you would like to use, this is just my guess based on yout image):

                  CASE ATTR([Data Type])
                  WHEN "Percent" THEN IFNULL(ABS([Last Month]-[Previous Year])/[Previous Year],1)
                  END
                  

                   

                  Text:

                  CASE ATTR([Data Type])
                  WHEN "Notes" THEN ATTR([Salesforce (Tableau help)].[Notes])
                  WHEN "Rep" THEN ATTR([Salesforce (Tableau help)].[Rep])
                  END
                  

                   

                  and set the format for each as desired

                   

                  7. Place these three calc fields on the Text shelf, and then edit the Label, using the "..." button from the Text landmark. This will remove the extra spacing added by default.

                   

                  label.png

                   

                  Is this what you are looking for?

                   

                  If you want more control, check out the route used here: https://public.tableau.com/profile/joe.mako#!/vizhome/AlternateMeasuresPlacementjmedit/AlternateOption and other comments at http://tableaufriction.blogspot.com/2012/09/putting-measures-in-middle-of-viz.html

                  3 of 3 people found this helpful