7 Replies Latest reply on Mar 31, 2016 4:12 AM by Takaaki Koseki

    How to Compare Sum() of two differnt ids

    Erin Yang

      Hi All,

       

      here's the scenario:

       

      1. I want to display the ratio of sum(sales) of two sales people (SP1 and Sp2) on a dash board, Sum(sale of SP1)/Sum(sales SP2)

      2. there are two lists(L1 and L2) on the dashboard to let user select Sp1 and SP2

      3. the contents of lists L1 and L2 are dynamically loaded based on other operations and can't pre-loaded

       

      since dynamic parameter is unavailable, it's hard to get this done.

       

      Any suggestions?

       

      Thanks,

       

      Erin

        • 1. Re: How to Compare Sum() of two differnt ids
          Takaaki Koseki

          Hi.

           

          It might be can solved by duplicating a data source as a secondary data source.

          The primary data source is for SP1 and L1, and the secondary data source is for SP2 and L2.

           

          In my sample workbook, [Sub-Category] is regarded as a sales people.

           

          v.9.1 attached.

          Thanks.

          • 2. Re: How to Compare Sum() of two differnt ids
            Erin Yang

            Thanks, Takaaki.

             

            I have 9.0 on my machine, and can't open your workbook currently. I had tried 2 data sources and added actions to let the selection from L2 control the secondary data source weeks ago, but the action didn't work on secondary data source, and it always returned total sales for all sale persons no matter if I selected the SP2 from L2 or not.

             

            I am wondering if 9.1 has the feature that enables filters/actions work on second data source? Could you do me a favor to explain what and how you use to enable L2 to control the secondary data source?

             

            Thanks,

             

            Erin

            • 3. Re: How to Compare Sum() of two differnt ids
              Takaaki Koseki

              Hi.

               

              Now I attached the v9.0 sample.

              As you see the sample, I didn't use an action filter but I used a quick filter.

              If you have something difficult to do in my way, please tell me!

               

              Thanks.

              • 4. Re: How to Compare Sum() of two differnt ids
                Erin Yang

                Hi Takaaki,

                 

                Just took a look at your workbook, but the filters in it doesn't work in my situation, since the values on lists L1 and L2 are dynamically loaded from some other operations on the dashboard(s), e.g., based on the region(s)/product(s) top 10 sales, and this is the hard part.

                 

                Thanks you much trying on this.

                 

                Erin

                • 5. Re: How to Compare Sum() of two differnt ids
                  Takaaki Koseki

                  Hi Erin.

                   

                  Now I can figure out what you want to do!

                   

                  Well...

                  One of the way to solve your problem is using the table join instead of the data blending.

                  Note that this way might be impact to the dashboard performance, so you can use it if you are tolerable...

                   

                  Now I created new data source using self join like below.

                  Orders for SP1, and Orders$1 for SP2.

                  20160325_datasource.png

                  The reason why I used [Country] for join key is just it was the same value across all of the data rows.

                  If you connect to database, you might use this ideal join.

                  Sub-Category <> Sub-Category (Orders$1)
                  

                  Note that here I regard [Sub-Category] as the Sales People.

                  Anyway, all I want to do here is to get the all combination of [Sub-Category].

                   

                  And then, one more trick is required because the data has increased effected by self join.

                  I created this calculated field in order to get one correct value for each rows.

                  SUM({ INCLUDE [Row ID]:MIN([Sales]) })
                  

                  Also for SP2.

                   

                  Finally, I created two action filters one is for SP1 and another is for SP2.

                  20160325_1.png

                  20160325_2.png

                  20160325_sol.png

                   

                  How about this way?

                   

                  Regards, Takaaki.

                  • 6. Re: How to Compare Sum() of two differnt ids
                    Erin Yang

                    Hi Takaaki,

                     

                    Thank you so much working on my issues.

                     

                    Joining two data sources(Custom SQL, tables, or views) works well when they returns small groups of data, but won't work on my situation with over more than tens of million rows most of time.

                     

                    But really appreciated!

                     

                    Erin

                    • 7. Re: How to Compare Sum() of two differnt ids
                      Takaaki Koseki

                      Hi Erin,

                       

                      Oh you have more than tens of million rows...

                      means you cannot be tolerable...

                       

                      I'm sorry that there is not a simple way to solve your question as far as I know.

                      If I were you, I'd try to create a master table of sales people in order to reduce joining data.

                      It might be a kind of hard work, but I would try to solve it from outside of Tableau.

                       

                      - Takaaki