8 Replies Latest reply on May 12, 2014 1:50 AM by vaishnavi.a

    Including Values in Sum Only If Not Duplicated Elsewhere

    Ian Conlon

      I've got data on sales that include a number of different salespeople, sale IDs, and sale values. My problem is that in many cases, there are several parts to each sale (therefore duplicated sale IDs) and multiple salespeople on a sale (therefore duplicated salesperson IDs on each sale). How do I get Tableau to return the value of a sale only if it is the first instance of the sale ID part in the partition? I imagine this will require use of a table calculation or a window() function, but I can't quite figure it out.

       

      To make this a little more concrete, here's a brief sample data set:

       

      SaleIDSalePartSalespersonIDValue
      0001Parts0001200.00
      0001Parts0002200.00
      0001Parts0003200.00
      0002Parts0002950.00
      0002Parts0005950.00
      0002Labor0002450.00
      0002Labor0005450.00
      0003Parts0009425.00
      0003Parts0010425.00


      In this case, I would want Tableau to read the value $200.00 for saleID 0001 (not the sum total of $600), $1,400 for SaleID 0002 (combining parts 1 and 2 of the sale), and $425 for saleID 0003. In addition, I would want Tableau to respond dynamically to the use of filters. So, if I filtered this data to show the sales totals for SalepersonID 0002, the value displayed would be $1,600 (which is $200 for saleID 0001 and $950 for part 1 of saleID 0002 and $450 for part 2 of saleID 0002). Finally, if I wanted to see the total value for SalePart "Parts", Tableau would show $1,575 (which is 200 from saleID 0001, 950 from saleID 0002, and 425 from saleID 0003).

       

      I've tried resolving this issue using my custom SQL code, but because I can't predict what filters will ultimately be applied, functions like row_number() and so forth won't do the trick. I'm sure the issue needs to be resolved in Tableau through the use of some kind of calculated field.


      I have a feeling that this sort of question has been asked before, but I couldn't think of how to describe it in a way to locate the right answer in the forum. I'm sorry if this is indeed a question already answered before.

       

      Thanks for the assistance!

        • 1. Re: Including Values in Sum Only If Not Duplicated Elsewhere
          Ian Conlon

          So, I found a *partial* solution to my question, but it messes with how the underlying data show up. The solution I found was from a different from response that said to do the following (for everyone's reference):

           

          1. Create a calculated field similar to the following:

           

          IF FIRST()==0 THEN

          WINDOW_SUM(MIN([Measure1]),0,IIF(FIRST()==0,LAST(),0))

          END

           

          2. Right click on [Dimension1] and select Duplicate.

           

          3. Place the [Dimension1 (copy)] on the level of detail shelf.

           

          4. Place the calculated field on the view.

           

          5. Right click and select Compute Using>[Dimension1 (copy)]

           

          This ends up providing the correct (distinct) sum totals, but whenever I hover over individual bars and go to the underlying data, it only shows a single sale's worth of rows. So, whereas there were once about 800 rows in my data, when I go to the underlying data now, it shows only 3 rows. I have a feeling it's due to the fact that I'm using a certain level of detial and the WINDOW_SUM(MIN function, but I really need for users to be able to drill down to see ALL of the underlying sales that produce the sum total shown. Any help would be greatly appreciated.

          • 2. Re: Including Values in Sum Only If Not Duplicated Elsewhere
            Ian Conlon

            Does anyone have an idea of what's going on with the underlying data? The values in my graph are now correct, but when I now drill down to the underlying data, only one sale "part" shows up, even though I know there are about 800+ parts that went into the summing calculation. Again, I'm sure this has something to do with the window calculation being used, but I haven't been able to figure out why or how to get around it (to show ALL relevant rows in the underlying data).

             

            Any help would be greatly appreciated!

            • 3. Re: Including Values in Sum Only If Not Duplicated Elsewhere
              Jonathan Drummey

              Hi Ian,

               

              I've got some ideas on this, one thing I want to know is that when you're talking about drill down, are you working with Tableau's hierarchies? Also, is it possible to have any screenshots of the views you are trying to create, including all of the Shelves?

              • 4. Re: Including Values in Sum Only If Not Duplicated Elsewhere
                vaishnavi.a

                Hi Jonathan,

                 

                I'm also facing the similar issue, I have just created a tabular report and applied Top N parameter for the same report.

                 

                • I went to Analysis tab-> view data-> it is displaying 10 rows (if Top 10 value is selected in Top N parameter)
                • In the report its displaying only 4 rows( remaining 6 rows are eliminated in report since they are duplicate rows)
                • Is it possible to display 10 rows when Top 10 value is selected in parameter  (duplicate rows should be eliminated and it should look for other values and take remaining 6 rows)

                Please let me know how we can achieve this.

                 

                Thanks,

                Vaishnavi

                • 5. Re: Including Values in Sum Only If Not Duplicated Elsewhere
                  Jonathan Drummey

                  Hi Vaishnavi,

                   

                  Please post some sample data in a packaged workbook and I'll take a look. Your written description doesn't cover how the Top N are returned (whether via Top Filter or using a table calc, I'm guessing a Top Filter), what the dimension being filtered is, what dimension(s) are in the view, etc.

                   

                  Jonathan

                  • 6. Re: Including Values in Sum Only If Not Duplicated Elsewhere
                    vaishnavi.a

                    Top N value I implemented it using parameter, User can type N value in parameter and they can see the Top N values ( Values are calculated "value" measure, I used index() and sorted it in desc order to get the Top N highest values)

                    • 7. Re: Including Values in Sum Only If Not Duplicated Elsewhere
                      Jonathan Drummey

                      Hi Vaishnavi,

                       

                      I'd like to help you, and you only posted some mocked up data, not a Tableau packaged workbook. There are 4 dimensions in the data, without a packaged workbook I don't know which of those dimensions are used in the view that is having problems, what the Compute Using of the INDEX() calc is nor what dimensions are being sorted. For a fuller explanation of why we keep asking for packaged workbooks, see http://community.tableau.com/docs/DOC-5065. Please use that mocked up data to create a packaged workbook that reflects the issue you are having and post it in a response to this question*** and I'll take a look.

                       

                      Jonathan

                       

                      *** If you just appended the workbook to your prior post, I wouldn't get an email notification of your update, so a new post is necessary.

                      • 8. Re: Including Values in Sum Only If Not Duplicated Elsewhere
                        vaishnavi.a

                        Thanks Jonanthan, I will soon post the sample workbook for this issue