5 Replies Latest reply on Aug 21, 2018 12:53 PM by Sheng Zhong

    Dynamic Numerical Comparison Question

    Sheng Zhong

      Hi, Tableau Fans

        I have a question I call "dynamic numerical comparison". Here is the situation:

      • I want to compare # of log-in vs. # of days for each supplier
      • # of log-in is varied by the date range based on parameters "Start Date" and "End Date"
      • # of days is also obviously varied by the date range based on parameters "Start Date" and "End Date"
      • If supplier's # of log-in >= 50%* # of days based on given date range, then this supplier will be fallen into "category 1"
      • If supplier's # of log-in <50% * # of days based on given date range, the this supplier will be fallen into "Category 2"

       

      Currently, the challenge is that I don't want to explicitly show supplier but just number of suppliers fallen in each category, Tableau will automatically sum all suppliers' # of log-in together. I just want to compare on each individual supplier level. And I have tried LOD calculation and window_sum. But they haven't worked out as what I expect. The following is what I expect:

       

      And I kinda of figure out a way to work around but it prevents me from building on next feature on dashboard. The following is the details of work-around:

       

      1. Created a calculated field for "# of days" = (DATEDIFF('day',[Start Date],[End Date])+1)
      2. Created a calculated field for comparision = IIF(sum([Log-in])>=1.3*AVG([# of days]), 'Category1','Category2')
      3. Created separated sheets, one for "Category1" and one for "Category2"
      4. I bring in supplier on dimension in order to do comparison on each individual supplier
      5. Then I hide the tile on supplier
      6. Count on supplier then turn to running_sum
      7. Just show the last records

      Although this work-around can get me to what I want, it definitely cause a issue when I build action filter on graph. Because when I click on "category1" number 2, instead of showing line graph for aggregated these 2 suppliers' # log-in vs. # trade, it just shows the 2nd suppliers'.

       

      I have attached my sample work on discussion forum.

       

      Big thanks for all your help

        • 1. Re: Dynamic Numerical Comparison Question
          Hari Ankem

          Here is your expected output:

          1.png

           

          And this is what I did:

          1. Created a calculated field for # of logins: {FIXED [Supplier]:SUM([Log-in])}

           

          2. Created a calculated field for Category 1: { FIXED [Supplier]:SUM(If [# of Logins]>=1.3*[# of days] THEN 1 ELSE 0 END)}

           

          3. Created a calculated field for Category 2: { FIXED [Supplier]:SUM(If [# of Logins]<1.3*[# of days] THEN 1 ELSE 0 END)}

           

          4. Added "Within Date" calculated field to filter and selected "True" and added it to context.

           

          Note: I have used 1.3 above instead of 1.5 so that we see both category values.

           

          Hope this helps.

          • 2. Re: Dynamic Numerical Comparison Question
            Okechukwu Ossai

            Hi Sheng,

             

            One way to make the line graph responsive to action filters is to use an LOD approach.

             

            Step 1: Create calculated field [Log-in (LOD)]

            {FIXED [Supplier]: SUM([Log-in])}

             

            Step 2: Create calculated field [# of days (LOD)]

            {FIXED [Supplier]: MIN((DATEDIFF('day',[Start Date],[End Date])+1))}

             

            Step 3: Create calculated field [Comparison (LOD)]

            IF [Log-in (LOD)] >= 1.3 * [# of days (LOD)] THEN 'Category1' ELSE 'Category2' END

             

            Step 4: Create calculated field [Comparison Filter]

            LOOKUP(MIN([Comparison (LOD)]),0)

             

            Step 5: Adjust the views

            Add [Within Date] filter to context in all the worksheets. Add [Comparison Filter] to the filter shelf of 'Work-around for Category 1' and 'Work-around for Category 2' worksheets.

            Add [Comparison (LOD)] to the Details marks card of 'Work-around for Category 1' and 'Work-around for Category 2' worksheets.

             

            Step 6: Set up the action filter like below.

             

            Below are the results.

             

             

             

             

             

             

             

            Hope this helps.

            Ossai

            1 of 1 people found this helpful
            • 3. Re: Dynamic Numerical Comparison Question
              Sheng Zhong

              Hi, Hari

              Thanks very much for your help!

              • 4. Re: Dynamic Numerical Comparison Question
                Hari Ankem

                You are welcome.

                • 5. Re: Dynamic Numerical Comparison Question
                  Sheng Zhong

                  Hi, Okechukwu

                  I very appreciate for your help and detailed information. But I have a few questions to ask for better understanding LOD:

                  1. Is it necessary to use fixed LOD to calculate "# of days"?
                  2. Why have to change the calculated field "within day" to context filter? just because we need to filter on first?
                  3. what is rationale for [Comparison Filter]=LOOKUP(MIN([Comparison (LOD)]),0)
                  4. What is rationale for putting the calculated filed in details mark.

                   

                  What if I introduce another data source (primary data source) and blend with the existing one and treat the existing one as secondary data source, how can I achieve the same action filter functionality by click on either number to filter on data on another data source?