1 2 Previous Next 23 Replies Latest reply on Oct 15, 2014 3:21 PM by Michel Caissie

    Cross-table several variables

    Vladimir Kostochka

      Hi guys,

      Is doable in Tableau make "crosstable"?

      I have a flat file with several variables from qID_19_Discovery to qID_482_Comparision

      Capture.PNG

      I want use them together in one table and add color expression based on each number.

      It's easy to do when all variables combine in one new variable.

      Anybody knows any tricks?

      Please find the attached file.

      Thanks,

        • 1. Re: Cross-table several variables
          Matt Lutton

          What kind of coloring are you wanting?  You can place Measure Names on the color shelf, but that just provides a color per each measure row in the unpivoted data shown in your sheet.

           

          The best way to accomplish this is what you've already suggested: reshaping the data so you have a single Dimension that categorizes the various types of measures you want to analyze.    There is an Excel reshaping tool (among others), or you can use Custom SQL to do this.

           

          If you cannot reshape the data, your options will be limited and more manual effort will be required, depending on what you're hoping to accomplish (which we still need to clarify).  You might want to look over this link, as it provides nearly everything you could ever want to know about Conditional Formatting inside Tableau:

           

          Older But Still Useful – Conditional Formatting | Drawing with Numbers

           

          Be sure to read everything contained therein, download the Tableau Public workbook, and read/study all the sheets and the various Captions that describe the work he has done.

           

          Hope this helps--if you can clarify what you want as an end result (what needs coloring, and based on what logic?), that should help in communicating your overall goal more clearly.

           

          Best of luck!

          • 2. Re: Cross-table several variables
            Vladimir Kostochka

            Thanks Matthew for reply.

            It's stat. test, which I calculate below

            Capture.PNG

            Instead of [Row] should be qID_19_Discovery, and so on...

            Color expression is

            Capture.PNG

             

            It works only with 1 variable with many tags.

            I have QlikView dashboard (where I can load crostable from flat file), my boss wants to switch to Tableau.

            I just want replicate the same dashboard in Tableau and check some possibility in Tableau.

             

            Also I found there is no possibility calculate simple average from 2 and more variables

            like rangeaverage(qID_19_Discovery, qID_Depth and so on...)

             

            avg(qID_19_Discovery) + avg(qID_Depth) + ..../ Number

            • 3. Re: Cross-table several variables
              Matt Lutton

              I'm afraid that doesn't help me much since the workbook you posted has no t-test measure.... so, now I'm even more confused.

               

              Your boss needs to understand that "switching" reports from QlikView to Tableau is not going to necessarily be a simple process.  They are different tools, with different features.  You should be able to replicate anything you've done in QlikView, but you may need to revisit your data and put it in a more ideal format for working with Tableau.

               

              Hope this helps.  Cheers.

              • 4. Re: Cross-table several variables
                Vladimir Kostochka

                I added to my Book2  color calculations (Folders  Discovery and Decision Making).

                Capture.PNG

                Color T-Test Discovery should work only for Discovery variable.

                Other should be black.

                Maybe is it possible to create calculated field with condition breaking by variables?

                • 5. Re: Cross-table several variables
                  Matt Lutton

                  As far as I know, this is not possible without either: separating out each measure on a new sheet, or reshaping your data.

                  • 6. Re: Cross-table several variables
                    Vladimir Kostochka

                    How can I do reshaping my data?

                    • 7. Re: Cross-table several variables
                      Matt Lutton

                      As mentioned previously, there are several ways -- using Custom SQL in Tableau is one way (but you'll need to know how to do this and I cannot guide you), or a reshaping tool like the Excel Reshaper.  A lot depends on where your original data is coming from.  Have you taken the time to look at the Conditional Formatting link I provided?  I think you'll want to look through it and study it in depth--I do not believe there is going to be a quick and easy solution here unless you're comfortable with reshaping your data on your own.

                       

                      Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software

                       

                      If a DBA or someone else is providing this data in a Database, perhaps they'll reshape the data for you?

                       

                      Here's a good reference point for preparing data for Tableau:

                      Preparing Data for analysis with Tableau | Tableau Public

                      1 of 1 people found this helpful
                      • 8. Re: Cross-table several variables
                        Vladimir Kostochka

                        Ok, thanks.

                        I'll play with reshaping tool.

                        Thanks again

                        • 9. Re: Cross-table several variables
                          Matt Lutton

                          No problem -- the tool is incredibly easy to use.  I provided another link above on getting your data ready for Tableau.

                           

                          I'd still recommend you spend a great deal of time with Jonathan's conditional formatting workbook, as it will be incredibly useful to you if you're coming to Tableau from another tool, like QlikView.

                          • 10. Re: Cross-table several variables
                            Matt Lutton

                            If you want to go through the hassle of using a separate sheet for each measure, you CAN do that.  I just would never recommend it, when a simple reshape of your data will make this MUCH easier to accomplish.

                             

                            But if you want to do the manual work, you can place a new sheet on a dashboard for each new measure, build a header for each new sheet, and only apply the coloring to the "Discovery" sheet.  I've set up two sheets in the attached, and placed them together on a dashboard.  On all the additional sheets, you'll have to uncheck "Show Headers" to hide the dates, and only have them appear on the first sheet on the dashboard.

                             

                            Sheets on Dash.png

                            Version 8.2 workbook example attached with the first 2 sheets set up.  You'd have to create and MAINTAIN several more sheets.  This setup has a lot of limitations, which are fairly obvious - more to maintain, more to organize on the dashboard, formatting has to be set up for each sheet individually, etc.

                             

                            So, again, I would NOT recommend doing this; you'll be much better off with a dataset that provides a Dimension (categorical) value that can then be referenced in a calculation.

                             

                            Cheers.

                            • 11. Re: Cross-table several variables
                              Vladimir Kostochka

                              No, it doesn't work.

                              If we don't collect one variable, it will be empty row.

                              • 12. Re: Cross-table several variables
                                Matt Lutton

                                I do not know exactly what you mean by this statement, as I would think that would be the case regardless.

                                 

                                But again, I'm not recommending you do this--Its just the only way you'll have access to color that one measure, without impacting everything else you want to display together.

                                • 13. Re: Cross-table several variables
                                  Matt Lutton

                                  I'm noticing now that this is survey data.  Aside from the resources on reshaping your data that I've already provided, I strongly urge you to utilize the resources freely available on analyzing survey data in Tableau:

                                   

                                  Visualizing Survey Data | Tableau Software

                                  Survey Data and Tableau | Tableau Software

                                  Survey Analysis | Tableau Software

                                  http://www.datarevelations.com/using-tableau-to-visualize-survey-data-part-1.html

                                   

                                  Best of luck.  What you want to do is possible, and re-shaping your data for Tableau is the answer.  You just need to figure out how to accomplish that, given your data source and your workflow.

                                   

                                  Be sure to mark the thread as answered eventually, as that will help others find the information you were seeking when you started this thread (again, unfortunately, there's no built-in/easy way for you to get what you want without additional work on the data you're connecting to) -- I've spent a great deal of my time with you (I'll point out that we are all volunteering here, nobody is paid) on this thread, sharing freely available resources that a Google search would turn up.  Your solution lies within those resources.  Its up to you to study those resources (and learn more about Tableau in the process), and subsequently apply a solution.

                                   

                                  The only way we can help further is if you provide us with the raw data, so we can apply the reshaping for you.  Or I suppose someone could jump in and create a Custom SQL solution for you, but that rarely happens on this Forum.

                                   

                                  Cheers!

                                  • 14. Re: Cross-table several variables
                                    Michel Caissie

                                    Vladimir,

                                     

                                    If you drag both  Color T-test Discovery  and Measure Names on the color shelf, you will be able to get what you want.

                                    (Ctrl-Click and right-drag)

                                     

                                    Michel

                                    1 of 1 people found this helpful
                                    1 2 Previous Next