14 Replies Latest reply on Nov 16, 2018 10:11 AM by Cody Kaiser

    Perform row level total on TabPy calculation

    Cody Kaiser

      I have a Python script that evaluates the cumulative distribution function correctly for each row of data. I would like to add a row total that is the average of all the row values. When i try and show row grand totals and change the calculation to average i get an error because python tries to then calculate at an aggregated level instead of rows.

       

       

      I was able to create a window_average calculation that shows the correct data, but i can't add the column to the far right as if it were a total. It just shows as a data value in each column.  

       

        • 1. Re: Perform row level total on TabPy calculation
          Deepak Rai

          Do you have some sample workbook?

          • 2. Re: Perform row level total on TabPy calculation
            Joe Oppelt

            Deepak -- Given that it's a python data source, I'm not sure he can upload that.  (I've never played with python, so I'm not sure about that.)

             

            Cody --- Probably what you are seeing is not really a python issue.

             

            Because your measure is a table calc, you're going to need a subsequent table calc to aggregate it (AVG, SUM, whatever.)  So your WINDOW_AVG is the right thing to do.  The problem arises in displaying it.  The sheet is dimensionally oriented.  You have columns for product spec, and if you wanted to make another column to shove in another value, you'd need some bogus spec in your data, and for those rows you could shove your window average.

             

            That's messy, and most people don't do it that way.


            Your best bet is to create a second sheet with all the same filters (use "apply to" to make one copy of a filter impact both sheets.)  Re-design the second sheet to display only the window-average, and position the two sheets on a dashboard to appear as one viz.

            • 3. Re: Perform row level total on TabPy calculation
              Joe Oppelt

              Bummer trying to open your workbook:

               

               

              You might be able to get your data into the packaged workbook following instructions here:

               

              Packaged Workbooks - Tableau

               

              Check out the part half way down the article for server-based data sources.


              Or, you can hack up a subset of your data in an excel file using the steps shown in the video linked here:

               

              Video demonstrates how to anonymize your workbook/data

              • 4. Re: Perform row level total on TabPy calculation
                Cody Kaiser

                Joe,

                 

                Since Python is expecting the row level detail I need to have the 3 fields "Prod Name", "Prodspec Name/Type" and "Data Name" show in either the columns or rows for it to calculate the script. If i move any of them from the rows or columns, to tooltip for example, it breaks the script. How can i re-design the second sheet to display only the window-average?

                • 5. Re: Perform row level total on TabPy calculation
                  Joe Oppelt

                  You'll still have all those on the sheet, but not necessarily on ROWS or COLUMNS.  (Most likely you'll drag [Data Name] to the detail shelf.)  When you do that, you'll get all x-many marks as there are Details in one pane for each Prod and Spec. 

                   

                  Drag your first table calc off measure values and put it in Details.  That leaves your AVG calc on the text shelf.  Finally you'll create a table calc to display only the first of the multiple marks in each pane.  Now you'll have only the final average displayed for each row.

                   

                  That's the simplified description.  You'll have to play with table calc settings to accommodate the movement of [Data Name] from Columns to Detail.  The row/column configuration of the sheet allowed default settings of TABLE(across) to work, but now there is no "across" on Tableau's mind.  You'll have to specify dimensional order.

                   

                  It's far easier to show you than to describe it.  Can you make a bogus data source in excel and swap data sources in a sample workbook?  Directions on how to do that are in the video linked here:

                   

                  Video demonstrates how to anonymize your workbook/data

                  • 6. Re: Perform row level total on TabPy calculation
                    Joe Oppelt

                    And you sent me a working twbx already..

                     

                    Opening it.

                    • 7. Re: Perform row level total on TabPy calculation
                      Joe Oppelt

                      Except the sheet comes up with no data.

                       

                      I tried taking off the filters, but I still get blank data.


                      So maybe you will have to try the bogus excel data source method.

                      • 8. Re: Perform row level total on TabPy calculation
                        Joe Oppelt

                        OK, Cody, I have it worked down to this.  I made an excel file with just the limited data you wanted filtered on your original sheet.  That's attached.

                         

                        I imported it as a new data source, and replaced data source.  There is some problem with [PSWL] and [PSWU].  Are these crucial to the problem?  Can you just change them for now so that they have some hard-coded constant value?

                         

                        What I want to end up with is the essence of your sheet with x-many columns, and the window average of those columns.  Then I can show you how to make the second sheet.

                        • 9. Re: Perform row level total on TabPy calculation
                          Cody Kaiser

                          The PSWL and PSWU are the python scripts that are performing a cumulative distribution function across each combination of: "Prod Name", "Prodspec Name/Type" and "Data Name". They are the key building blocks to all of the calculations performed after. I can hard code them as a test if needed, but will need them to work in the final workbook.

                           

                          The script looks like this:

                          SCRIPT_REAL("from scipy.stats import norm
                          import numpy as np
                          return list(np.where(np.isnan(norm.cdf(_arg1, _arg2, _arg3)),None,norm.cdf(_arg1, _arg2, _arg3)))"
                          ,ZN(ATTR([LCL])), ZN([Mean_DataPassing]),ZN([StdDevP_DataPassing]))

                          • 10. Re: Perform row level total on TabPy calculation
                            Joe Oppelt

                            The numbers in the [Data Name] columns don't matter -- for the demonstration of the example here.  You'll take what I show you in the sample and do the same in your actual workbook.  The fact that it's python there won't matter.

                            • 11. Re: Perform row level total on TabPy calculation
                              Cody Kaiser

                              I made the requested changes to the worksheet as requested. The calcs are set as needed to work just from the spreadsheet.

                              • 12. Re: Perform row level total on TabPy calculation
                                Cody Kaiser

                                Based off your response I should be moving data name to detail, which keeps the calcs intact, but allows to add a total column?

                                 

                                • 13. Re: Perform row level total on TabPy calculation
                                  Joe Oppelt

                                  In the attached my goal is to create a TOTALS sheet for you.

                                   

                                  Sheet 4 is your original sheet.  For the sake of simplicity I remove all but 3 measures, and I adjusted columns so that it all fits on one sheet without scrollbars for now.

                                   

                                  Sheet 5.  I made 3 corresponding window_whatever calcs and added them to the sheet.

                                   

                                  Some things to note here.  By default they go on the sheet with the setting of WINDOW(across).  And here, that default is a correct setting to achieve the proper results.  But I will be taking the [Data Name] dimension off columns eventually, which removes the concept of "across".  So I want a setting that is independent of the positional relationships of the dimensions on the sheet.  Right click one of the table calcs and select "Edit Table Calc".  You'll see this:

                                   

                                  I clicked "Specific Dimensions" instead of "table(across)".  And then I selected all dimensions, and reordered the dimensional list to the order you see.  I need Data Name last.  And I need Prodspec to be after Prod Name.  This tells tableau to run through the dimensions in that specific order, cycling through the [Data Name]s last.  And I selected Prodspec as the "restart" dimension.  Each time we get a new Prodspec, reset the table calc.  You can see that this becomes the logical equivalent of what's on that sheet.

                                   

                                  I have that setting for all table calcs on this sheet.

                                   

                                  On Sheet 6 I dragged the original measures from the Measure Values shelf to the detail shelf.  This leaves only the three table calcs getting displayed.  Notice that the number in each row is the same along the row.

                                   

                                  On Sheet 7 I dragged [Data Name] from COLUMNS to Detail.  Now we get x-many copies of the table calc value in each cell.  That happens because [Data Name] is still on the sheet, but not on rows or columns.  We need this, because we need those individual Data Name values for the  table calc to act upon.  And because we specified dimensional order in the table calcs, Tableau knows how to address the values properly.

                                   

                                  On Sheet 8 I added one more calc.  [index].  I also set it with the same table calc settings.  I want a 1-through-N value to use in the next step.

                                   

                                  On sheet 9 I dragged [index] to filters and selected for value 1 only.  Now I just get 1 value in the cell.  Also I right clicked on the pills in ROWS and unchecked "show header" so that we don't have those columns showing up.  Also I double clicked in COLUMNS and that gave me a mini edit window where I typed "Grand Total".  That created a local dimension value.  I clicked on the "Grand Total" label header this creates, and selected "hide header".

                                   

                                  Now I have a total sheet that corresponds to the original sheet.

                                   

                                  On Dashboard 1 I floated Sheet 4 and Sheet 9, and nudged sheet 9 to display as if it appears like part of the original sheet.

                                  1 of 1 people found this helpful
                                  • 14. Re: Perform row level total on TabPy calculation
                                    Cody Kaiser

                                    That worked brilliantly! I was able to create the report as needed. Thanks again for spending the time to teach me some advanced functions. It will be well used.

                                     

                                    One note that I wasn't aware of until you showed me was that when choosing a table calc to compute using specific dimensions, you can order the fields properly by dragging them.

                                     

                                    2018-11-16_10-04-06.png