13 Replies Latest reply on Dec 27, 2011 3:22 AM by saras ravi

    Still trying to sum three calculated fields!

    Jeff Roberts

      This is a followup to my post from two days ago, which has not been answered.

       

      I restructured some of my data so that three worksheets in Excel can be joined on a common field. From this data, I have calculated fields in each of three Tableau Public worksheets. In a fourth Tableau worksheet, I merely want to sum the results of the calculated fields from the three other worksheets. I thought that joining my data would solve this problem. But depending on how I write this fourth calculation, it either gives me the wrong answer or tells me that I cannot combined aggregated and non-aggregated values. I have tried the attr function but that hasn't helped.

       

      Here is a link to the worksheets so that they can be downloaded if someone is willing to help. I haven't figured out how to save this as a twbx file.

       

      http://public.tableausoftware.com/views/combined/combined?:embed=yes&:toolbar=yes&:tabs=no

       

      Thank you,

       

      Jeff

        • 1. Re: Still trying to sum three calculated fields!
          Richard Leeke

          Well I had a quick look without trying to understand what the calculations are about.

           

          There are 3 reasons why the combined total isn't equal to the sum of the three individual ones. 

           

          1) Your individual sheets use SUM() whereas the combined uses ATTR() for 2 of the 3 values.  That has the effect of just taking the value for 1 row, rather than all 132 rows.

           

          2) You use a different version of the calculation for Extra Sales.

           

          3) You have a filter in play on the sales sheet (which affects the value of [ExtraSales_V2] - but tthat filter isn't in play in the combined sheet.

           

          Here are you individual sheet calculations:

           

          SUM(AdditionalIncome2025)

           

          SUM(ExtraSales2025_V2)  (with a filter in play)

           

          SUM(State Savings 2025)

           

          and here's what you have in combined:

           

          attr([AdditionalIncome2025])+[ExtraSales2025]+attr([State Savings 2025])

           

           

          I've uploaded a version to Public with a couple of sheets just listing all those values - one with the filter in play (sheet5) and one without (sheet6).

          • 2. Re: Still trying to sum three calculated fields!
            Jeff Roberts

            Thank you for looking at this, Richard. Is it possible to structure these worksheets and the calculations so they can accurately be summed together? Separately, they work just fine. But I need to combine them. Any suggestions?

             

            There are two versions of ExtraSales because I was experimenting with different ways to structure this. Same goes with my use of attr. Didn't work obviously.

             

            Jeff

            • 3. Re: Still trying to sum three calculated fields!
              Richard Leeke

              Well assuming the three individual sheets are correct I think the real thing which is causing you trouble is the filter.  That means that the sales sheet is working on the rows which meet the filter criteria (52 rows) whilst the other sheets are all working off all 132 rows.

               

              There are undoubtedly ways around that, possibly involving structuring your data differently or duplicating your data connection and using data blending.

               

              But looking back at your earlier post, your best bet may just be to go back to your original 3 sheets in an Excel workbook and combine those with data blending.  I think it will be easy to get past the problem you were having.  I saw your post the other day and mentally filed it under the "wait and see if anyone else answers it first" category, but didn't remember to get back to it.

               

              I suspect your problem might have been that Tableau was insisting that you provided it with dimensions to link the data sources - but as you only want a single summary value, you haven't got a suitable field.

               

              If that is the case you can just invent one.  Create a calculated field called [Join Key] (say) in each of your three connections, and just set the value to some constant value (like "X").  Blend your datasources on those fields and then you can just have a calculated field which is defined as the sum of your calculations from each of the three data sources.

               

              Post a link to a workbook showing how far you've got if you get stuck.

              • 4. Re: Still trying to sum three calculated fields!
                Jeff Roberts

                Thanks very much for your help, Richard. I created a new Tableau workbook with the three Excel worksheets but did not link them this time. I created a calculated field called [Join Key] in each connection but now am stuck as to how to blend them in the "combined" tab and use the TotalRevenue calculation to add the three values together. As you can probably tell, I want the user to be able to check which services to tax under "sales" and set the rates in the other tabs. In the combined tab, ultimately, I would like to list the three values in a table along with their sum and a bar chart showing the total value in relation to a preset goal.

                 

                I did something like that here: (http://www.du.edu/economicfuture/bridgethegap.html) but it wasn't complicated by the filter.

                 

                The new test workbook is at this link.

                 

                http://public.tableausoftware.com/views/CombinedTest/combined?:embed=yes&:toolbar=yes&:tabs=yes

                 

                Jeff

                • 5. Re: Still trying to sum three calculated fields!
                  Richard Leeke

                  I've posted a modified version showing how to do the blending and the calculation here.

                   

                  I made the services data source the primary in the combined sheet and also made the filter on the sales sheet global, so it would also apply to the combined sheet.

                   

                  The combined sheet just shows the values of the fields used in the three sheets and the [CombinedTotal] calculation just adds them together.

                  • 6. Re: Still trying to sum three calculated fields!
                    Jeff Roberts

                    Thank you, Richard. I noticed that the table disappears if all the services are unchecked. Is there a way to populate the table with a zero if all the services are unchecked? A conditional statement?

                     

                    Jeff

                    • 7. Re: Still trying to sum three calculated fields!
                      Jeff Roberts

                      I've tried using IFNULL to populate the table with a zero, but the table still disappears when all the services are unchecked.

                       

                      [ExtraSales2025] = IFNULL((((sum([Revenue])+139693372)*[SalesTaxRate])-4051108)*1000,0)

                       

                      Jeff

                      • 8. Re: Still trying to sum three calculated fields!
                        Richard Leeke

                        Yes, I meant to mention that that would happen.

                         

                        The reason the IFNULL() doesn't help is that that applies if you have a row which has a NULL value in it.  With all the filter options cleared, you don't even have a row at all.

                         

                        The only things I can think of immediately are all horrible (like inserting an extra dummy row in the list of services, with all zero values and a vaguely instructional title - like "Zero value - leave selected").  Yuck.  Or maybe completely restructuring your data again - to combine it all into a single data source - but with distinct rows for the 3 types.  That way, deselecting all services wouldn't hide the other two types.

                        • 9. Re: Still trying to sum three calculated fields!
                          Richard Leeke

                          Edit: Whoops - double-post.  It's very hard to suppress the temptation to hit "Save" again when nothing seems to be happening!

                          • 10. Re: Still trying to sum three calculated fields!
                            Jeff Roberts

                            I had earlier experimented with restructuring the data into three rows, but then each of 130 or so services would have to be a column, right? I couldn't figure out how the services could then be selected.

                            • 11. Re: Still trying to sum three calculated fields!
                              Richard Leeke

                              What I was thinking (which is a bit ugly) was that each row would have a row type with one of three vales (Additional Income, State Savings or Extra Sales) and have 1 row each for income and savings and 130 odd rows for sales.  Include the columns for all three types in every row - with zero or NULL for the columns that don't apply to the type.  You'd need to have some sort of special value for the service type for the income and savings rows (possibly just NULL), so that you could keep those selected even if there are no services selected.

                               

                              This sounds very ugly - I feel sure there has to be a clean way of doing this...  ;-)

                              • 12. Re: Still trying to sum three calculated fields!
                                Jeff Roberts

                                If I can't figure that out, I'll probably add a note warning that at least one service must be selected. Thanks again for all your help, Richard.

                                 

                                Jeff

                                • 13. Re: Still trying to sum three calculated fields!
                                  saras ravi

                                  Hi,

                                   

                                  I have question on how to display sum( distinct revenue) by an attribute. For example, i have my raw data file has 3 columns Major Initiative ,Opportunity Id and Revenue.

                                  Major-Initiative Opty-Id Revenue

                                  Cloud              1            10

                                  Cloud              1            10

                                  Cloud              2            20

                                  Virtualization    2            20

                                   

                                  I need to get my results as show below:

                                  Major-Initiative CountD(opty id)  Revenue

                                  Cloud                2                      30

                                  Virtualization    1                      20

                                   

                                  Can somebody tell me how can i accomplish this.

                                   

                                  Thanks,

                                  Saras