1 2 Previous Next 15 Replies Latest reply on Mar 5, 2019 4:30 PM by Tony Liu

    documenting relationships between calculated fields

    keith.r
    keith.r Oct 30, 2014 9:02 AM

      I'm just curious how other people do this, or if there is some type of built-in functionality that might help.

       

      I have a large, complicated workbook with about 10 "levels" of calculated fields (well over 100 CFs). I don't have control over the source data, so all of my calculations need to be in Tableau. I'm attaching a simplified picture of sample calculations (only 4 levels) needed just to show side-by-side comparison of net profit per item under four conditions; YTD, Sept-only, during an advertising campaign, and after the advertising campaign. My real workbook has more layers than this. The 4 date filters are necessary (as far as I can tell) to be able to show these measures side-by-side on a worksheet.

      TableauCalcPic.png

       

      Rather than trying to keep track of the relationships manually in Visio, is there a better way? The problem arises when someone wants to change some aspect of the calculation (like adding a new filter at an intermediate level of calculation), and I need to be able to quickly see exactly which calculated fields need to change, and what downstream fields will be affected (for revalidation purposes). I know that Tableau shows downstream fields in the bottom of the Calculated Field Wizard, but I haven't found a way to expand that list to show all members, it seems to be limited to the width of the window.

       

      In a perfect world, I'd love for some way to export the data and have it create a visio chart for me, but I'll be happy for anything better than manual mapping.

       

      Thanks for suggestions and best practices!

        • 1. Re: documenting relationships between calculated fields
          Shawn Wallwork
          Shawn Wallwork Nov 1, 2014 11:38 AM (in response to keith.r)

          Keith that's pretty cool, and I like this Idea (the Visio idea), but I'm fairly certain there is nothing currently in the Desktop product that can do this. (I could be wrong.) If you post this in the idea section I'll vote for it.

           

          Cheers,

           

          --Shawn

          • 2. Re: documenting relationships between calculated fields
            Shawn Wallwork
            Shawn Wallwork Nov 1, 2014 11:41 AM (in response to keith.r)

            Thanks K! I hadn't yet voted for that second one.

             

            --Shawn

            • 4. Re: documenting relationships between calculated fields
              keith.r
              keith.r Nov 5, 2014 12:59 PM (in response to keith.r)

              In case it helps anyone else- Here is an Excel file to extract the fields and dependencies. No guarantees (not tested on different versions of Excel, or against different versions of Tableau). Developed on 32-bit Excel 2010, used against files created by Tableau desktop 8.1.

               

              The code is unlocked, so anyone can open with macros disabled and inspect the code before using it.

               

              I already got what I needed for my current project so I don't have any plans to tweak or further develop this, but anyone else is welcome to do so.

              2 of 2 people found this helpful
              • 5. Re: documenting relationships between calculated fields
                Chris Gerrard
                Chris Gerrard Feb 4, 2015 3:44 PM (in response to keith.r)

                This doesn't do the full job but it's a pretty good head start: http://tableaufriction.blogspot.ca/2015/02/more-calculated-field-analysis-fields.html

                It's a Ruby script that identifies the calculated fields and the fields they reference, saving the results in CSV files. There's a simple Tableau workbook with some basic analyses of the relationships.

                 

                I've been trying to come up with a general layout for auto-diagramming the relationships, but after sitting on the basic parsing code for a while didn't want to let the lack of diagramming hold it back.

                 

                If anyone has any thoughts on what good diagrams would look like I'd appreciate hearing them. So far I've been using GraphViz for diagramming but am thinking about seeing what D3.js and Neo4j can bring to the party.

                • 6. Re: documenting relationships between calculated fields
                  Neerav Makwana
                  Neerav Makwana Jul 11, 2018 9:26 AM (in response to keith.r)

                  This looks really helpful Keith, thanks a lot. Unfortunately I get an error when I try running it.

                   

                  Does this macro still run flawlessly for you or have Tableau updates broken it?

                  • 7. Re: documenting relationships between calculated fields
                    keith.r
                    keith.r Jul 11, 2018 1:58 PM (in response to Neerav Makwana)

                    I've changed roles (and company) and no longer use Tableau, but depending on the complexity of the problem, I'd be happy to help try to troubleshoot.

                     

                    If you have a couple of small sample workbooks in Tableau's current format (without data!) you could PM them to me via this forum and I can look at and try to determine what has changed- that would be the easiest/fastest option. Please, please make sure any proprietary data has been removed

                     

                    If you don't have any files that can be cleared of data and shared, we can go down a more complicated path of you posting back more info about the error:

                    • The actual text of the error message
                    • Click debug, and in the code window that pops up look for the highlighted a line of code
                      • share a screenshot showing which row is highlighted, and
                      • move your mouse over each word in that line, and if a pop-up shows for any of those words ( a pop-up will show the current value of that word), share a list of which words had a pop-up, and what value was shown in the pop-up.

                    that might give me an idea of what is going on, but remote troubleshooting is fairly difficult

                    • 8. Re: documenting relationships between calculated fields
                      Neerav Makwana
                      Neerav Makwana Jul 13, 2018 7:02 AM (in response to keith.r)

                      Hi Keith,

                       

                      Thank you for trying to help out. I have found a java tool which does the exact same thing so my objective has been met. I highlighted these issues to you in case you are still developing/enhancing your excel tool. So if you don't really require this tool, don;t invest much time and effort into this. Unfortunately I can't share my workbooks. I tried creating a dummy workbook and recreate the problem (by creating a formula which was very similar to the one that caused in error in your tool) but your macro processes it correctly.

                       

                      So i'll list out the errors that I'm getting in your macro. I'm getting error 5 in the following:

                          userauth = MsgBox("This macro will delete everything in columns A:E of the active worksheet- are you sure you want to do this?" & _

                                      vbCrLf & vbCrLf & vbCrLf & _

                                      "No warranty or guarantee, use at your own risk", , vbYesNo, "Warning: Cell overwrite = potential for data loss")

                       

                      Changing to the following resolves it:

                      userauth = MsgBox("This macro will delete everything in columns A:E of the active worksheet- are you sure you want to do this? Warning: Cell overwrite = potential for data loss")

                       

                      When I run the code, it gives error 5 further down the line too. Do you think this will help resolve the problem? I tried debugging by using mouseover for values, but did not understand what is causing the error.

                      • 9. Re: documenting relationships between calculated fields
                        keith.r
                        keith.r Jul 16, 2018 10:08 AM (in response to Neerav Makwana)

                        If the parameters on Msgbox are causing problems, it may be an issue with

                        (a) the operating system; are you running on a Windows PC or something else?

                        (b) using a non-local version of office;  are you using a local install or Office 365?

                        (c) using a non-English version of Excel where some of the hardcoded parameters are language-specific; is your local install of Office an English language install, or something else?

                         

                        I'd suggest just commenting out the message boxes, but the first one is actually collecting data from the user- so in addition to commenting out all the lines of the userauth = MsgBox (three lines total) you'd also have to add a line

                        userauth=6

                        right before

                        if userauth=6 then....

                         

                        Of course by doing so, you are eliminating the warning message and the opportunity to abort and save a copy of whatever is currently in the workbook before it is deleted.

                         

                        I'll wait for your reply on the 3 questions above, and if I can get a TWB file (from anywhere) I can test on my end to see if the original code needs further modification

                        • 10. Re: documenting relationships between calculated fields
                          keith.r
                          keith.r Jul 17, 2018 3:10 AM (in response to Neerav Makwana)

                          I was able to get a TWB from another source, and saw the same errors you described. I am using Win7 x64 Excel 2016 x32 (local install), so I suspect there was a windows patch that changed how the messageboxes work.

                           

                          Because that initial "do you really want to overwrite any old data" isn't critical to the workbook, I removed it (commented it out). I also changed one other line to remove an error I found with this sample TWB that never happened with my workbooks back when I was an active Tableau user.

                           

                          I've attached an updated copy here, please give it a try and let me know if it works or not.

                          • 11. Re: documenting relationships between calculated fields
                            Neerav Makwana
                            Neerav Makwana Jul 17, 2018 6:01 PM (in response to keith.r)

                            It's working perfectly now Keith.

                             

                            I noticed that this workbook (like the Java tool I mentioned in my previous post) capture formulae of calculated fields in terms of internal field/parameter names (also called IDs?) and not in terms of parameter names specified in the UI.

                             

                            Is there a way you can replace these IDs with actual names? Even though you'll be able to work on it after months, the excel macro you have shared is also immensely helpful. Thanks a lot!

                            • 12. Re: documenting relationships between calculated fields
                              keith.r
                              keith.r Jul 18, 2018 3:51 AM (in response to Neerav Makwana)

                              The sample TWB I acquired to test/fix the code is from a project I'm not familiar with, so I can't use it as a base from which to make any type of changes (I just don't know if the output is meaningful).

                               

                              If you have the time and inclination, create a new TWB off the Tableau training data or a public data source. Add 3-5 calculated fields (what they are doesn't matter).

                               

                              Run the macro, then add a column and write in the UI variable name, and what name it is replacing (I'm assuming you are referring to column A. I'm assuming that you are somehow renaming the calculations; is you are inserting those into tables and want to retrieve customized column names, I think that is a whole different animal (because you may not use every CF in your tables, and you may use some CFs more than once)

                               

                              If you do create a simplified TWB, send it my way and I'd be happy to take a look and see whether the changes you'd like are within my skills & bandwidth

                               

                              Example:

                               

                              ABC"What I would prefer to see"
                              UAC Count Redif [Calculation_3549399458068578309]= Red  then 1 else 0 ENDif UAC Risk= Red  then 1 else 0 END Instead of UAC Count Red, the UI name [in the Calculated Field editor]  is "UAC failed"
                              • 13. Re: documenting relationships between calculated fields
                                Tony Liu
                                Tony Liu Feb 28, 2019 4:03 AM (in response to keith.r)

                                I have built a shiny app to parse the .twb file and visualise the dependency between calculated fields with the exportable list of variables (performance depend on traffic): https://tony-yanjun.shinyapps.io/Tableau_zen_parser/

                                The code is here: https://github.com/33Vito/Tableau_Zen_Parser

                                 

                                Feedbacks are most welcome!

                                2 of 2 people found this helpful
                                • 14. Re: documenting relationships between calculated fields
                                  Natalie Kossman
                                  Natalie Kossman Mar 5, 2019 12:55 PM (in response to Tony Liu)

                                  This is super helpful Tony, so thank you for this!

                                   

                                  One note - for the Calculated Field Table, the export to Excel or CSV only exports one page of fields at a time. So 60 fields show up on 6 different pages by default. I think it would be useful to have a checkbox to  'Show All' records on one page or something to get a 'data dump' when you export.

                                  1 2 Previous Next