3 Replies Latest reply on Sep 13, 2016 3:14 AM by Simon Runc

    Finding %difference between 2 filterable funnels

    Eva Gonzalez

      Hi All,

       

      I am currently trying to create a dashboard that has two funnels which are shown side by side, with a % difference graph in the middle to show the difference in values between each stage of both graphs. To make it more complicated, each funnel has a bar chart underneath that filters it, so that we can compare how different units do against each other.

       

      I have been having trouble making both funnels interact correctly with the difference bars, I can get one to interact correctly using the “funnel %age candidate id difference” as:

       

      COUNTD([Candidate Identifier])/TOTAL(COUNTD([Candidate Identifier]))

      -SUM({FIXED[Step: Aggregates Selection Step]: COUNTD([Candidate Identifier])}/{COUNTD([Candidate Identifier])})

       

      However trying to get both to interact correctly is a bit of a nightmare. Does anyone know if this is possible to do, or have any ideas on how to do it? I have attached the tableau file to use with it.

       

      Many thanks,

      Eva

        • 1. Re: Finding %difference between 2 filterable funnels
          Simon Runc

          hi Eva,

           

          Hope you are well? As mentioned in my IM to you...always recognize one of my own children!!

           

          So yes this is a very tricky problem to get this to behave exactly how you want. There are a couple of solutions (that spring to mind, there may be others) and before launching into one or the other, I just want to see which of these 2 seems the best;

           

          solution 1 - Not tried this, but in theory, would allow the Viz to work exactly as you want, but the drawback is that you'll need to duplicate the data-source....so you'll have 2 identical datasource, both can be refreshed together. In fact in your final solution you could slim one right down so it only contained the fields and calcs for this Viz

           

          Solution 2 - I've attached this, so you can better understand how it would be used. This changes the behavior, in that the selection is made from just one bar-chart (using CTRL+Select to choose 2 Units). So the advantage is that it only needs one datasource, but would need a little more instruction for the user so they know to choose 2. The attached is a light mock up, and has issues, such that when nothing is selected it doesn't show all the Units, user can choose 3...etc. but if this one flies, we can sort all that out with some filtering rules. Also don't worry that things aren't lined up, I've left a few fields exposed so I can explain how it works, if this option is viable.

           

          Have a play, and let me know if this (option 2) works for you and if so, I can explain how it all works, and show you how we can fix the issues. Otherwise i think it'll have to be the second data-source.

          1 of 1 people found this helpful
          • 2. Re: Finding %difference between 2 filterable funnels
            Eva Gonzalez

            Hi Simon,

             

            Thank you for this, it is very helpful! Solution 1 could get data heavy so I have been having a play around with solution 2. I am slightly concerned that unit 1 and 2 are automatically selected, rather than two "total" funnels, but the functionality is perfect. I quite like that it is all in the one barchart, and I am not concerned about having to teach users how to use it.

             

            Do you mind taking me through it, and explaining how you built certain areas?

             

            I would like to rebuild the bars a little as well so that there is the option to compare unit to unit, and unit to a fixed total. I am aware that I could use a parameter to swap the funnels in from a different worksheet that uses the original settings, however if there is a neater solution that would be prefered.

             

            Thank you so much for your time!

            • 3. Re: Finding %difference between 2 filterable funnels
              Simon Runc

              hi Eva,

               

              Cool, so glad the "Single Bar Chart" solution flies!...so now the tricky bit explaining how it all works!! I have also added in the part, which shows the "Total Funnel" until the user selects a [Breakdown] from the bar-chart. I'm also ignoring the "single breakdown vs total difference" for now (as you'll see the following is reasonably complicated...and I find it much easier to tackle problems one-by-one!...but yes the best/easiest way to do this is going to be a parameter)

               

              Quick overview of how it works;

              I have created 2 new versions of the funnels, which are created to show the funnel for selected [Breakdown], and I have another version of the Funnels which (is set up to) show "Total Funnel". We then set up an "Action Triggered Sheet Swap" (a very nifty trick!) to swap out the "Total Funnels" when the user selects a [Breakdown] from the bar charts (this also swaps in the Difference Bar Chart, at the same time).

               

              Creating the [Breakdown] versions of the Funnels;

              First I bring in [Breakdown] into the Column Shelf, so we get a Funnel per [Breakdown]. I then add an index() calculation (this is just a counter), and set the compute-using (it's a table calc) to [Funnels: Breakdown]. I then, on one of them, add the index() to the filter and set to =1. On the other one, I do the same, but this time set the index() filter to =2.

               

               

              This way they will only show either the 1st or 2nd selection from the bar chart (when a [Breakdown] is selected from the bar chart)

               

              The difference Bars;

              Again I bring in the [Funnels: Breakdown] into the column shelf, on the Difference Bars, and use the following calculated field  to work out the difference

               

              [Funnels: %age of CandidateID - Diff]

              LOOKUP([Funnels: %age of CandidateID],FIRST()) - LOOKUP([Funnels: %age of CandidateID],LAST())

               

              As we now have nested Table Calcs, we can set up each calc to have a different compute-using...and is

              and

              In the Calculated Field: section you'll get a drop-down so you can individually set the compute-using for each element.

               

              As we are using FIRST() and LAST() all [Breakdowns] have the same difference. So to only show one, I add an index() (again compute using = [Funnels: Breakdown]) and set the filter to =1. The reason for using the index() here is that as a Table Calc filter, it is applied last. This means that we are filtering the view (just show 1 difference bar), but not the underlying data (so the difference still has access to the [Breakdown] to perform the difference calc. This is due to Tableau order of operations (which I can go into if you wish!)

               

              So this is how we achieve the difference bars from the selection.

               

              so the final bit is the action driven sheet swap...the general technique is written up here Sheet Swap with Action Filters...the simple version! 

               

              so I create the "Total Funnels" version (the same as the above, but with [Breakdown]/index() removed from the columns), and add this to the dashboard. I then also create 2 (dummy) dimensions "1One" (which is just the number 1) and "0Zero" (which is just the number 0).

               

              I then add the [1One] to the "Funnels: First", "Funnels: Second", and "Funnel: Diff_Breakdown" tabs. and add the "0Zero" to the "Funnels: Bars". I then set up (explained in the above link) the following 2 actions

               

               

              And so now, when the user first comes to the dashboard, it just shows the total bar. When they select a [Breakdown] from the bar-chart, it triggers a sheet swap to collapse the "Total Funnel" and show the 2 [Breakdown] funnels, but as the 2nd funnel is set to index()=2, it doesn't show (and the FIRST() and LAST() are the same in the difference bars, so this just shows a zero variance). When they select a second [Breakdown], the 2nd Breakdown funnel now shows, and the difference can do a comparison, as it has 2 breakdowns to compare.

               

              Currently, the Funnels/Bars don't collapse fully, as they have their titles (which won't collapse), but we can fix this (from the link above) you'll see I set up a 2 'Title' sheets which then collapse and appear with their respective worksheets.

               

              So quite a bit to take in there!...so I'll let you diagnose that, and check you are happy with what it's doing (and how it works), and then we can tackle the next part.

              1 of 1 people found this helpful