5 Replies Latest reply on Aug 5, 2019 12:40 PM by Jackson Kibler

    Workaround for a table calculation within a LOD

    Jackson Kibler

      I know Tableau currently doesn't support the ability to use a table calculation within a LOD and am hoping I can get some ideas on a workaround. The use case is as follows:

       

      I have a table with Loan IDs identifying applications disqualified for various reasons. Loans going through without disqualifications are accepted resulting in a conversion rate of (count of accepted)/(count of assessed applications).

       

      The DQ category is a column with each reason being a value so the data is tall as opposed to wide with each category being an individual column. This is because the user needs to be able to dynamically bucket the reasons into a couple groups. Loans can be disqualified for multiple reasons. Below is the general structure of the data:

       

      Loan IDCategory
      1001A
      1002B
      1007B
      1002D
      1006A
      1001C
      1011E
      1016F
      1001F
      1007B
      1003A

       

      In the table you can see that loan 1001 has multiple records for category 'A', 'C' and 'F'. The use case is to report on the loans disqualified in each category in a waterfall.

        

      CategoryCountWaterfallNote
      A33N/A: first category, no loans prior
      B321 loan in B was already counted in A
      C101 loan in C was already counted in A or B
      D101 loan in D was already counted in A, B or C
      E11No loans in E had been counted in A, B, C or D
      F211 loan in F was already counted in A, B, C, D or E

       

      In this case the raw counts of loans in each category differ from the waterfall if the loan had already been excluded due to a prior disqualification. Normally we could write this into the data, but there is another requirement to allow the user to dynamically change the sort order. For example, the user wants to be able to switch the order such that 'C' is first so the value for waterfall will change to:

        

      CategoryCountWaterfallNote
      C11N/A: first category, no loans prior
      A321 loan in B was already counted in C
      B321 loan in A was already counted in C or A
      D101 loan in D was already counted in C, A or B
      E11No loans in E had been counted in C, A, B or D
      F211 loan in F was already counted in C, A, B, D or E

       

      With the new order, 'C' and 'A' have values of 1 and 2 for Waterfall in lieu of the original 0 and 3, respectively.

       

      In a perfect world I could create a calculated field [Filter]: {Include [Loan ID]: RUNNING_COUNT(COUNT([Loan ID]))} calculating on Table Down

      I could then set [Filter] as a filter to only show less than or equal to 1. Anytime a row with a [Loan ID] already counted above it, would have a value of 2 for [Filter] and therefore be filtered out.

       

      A solution for when [Loan ID] is on the view works Running count by date, but to apply it when [Loan ID] is off the view is necessary to meet the requirements.

       

      Any help or ideas would be greatly appreciated, I couldn't find anything exactly like it in the Ideas, but this would probably also work: https://community.tableau.com/ideas/1604#comment-21193

       

      Thanks

       

      -Jackson

        • 1. Re: Workaround for a table calculation within a LOD
          Stephen Rizzo

          See attached. This will only work if your number of categories is relatively small. The basic idea was to encode the combination of categories into a single value for each ID, then parse that code to calculate the waterfall field value. Depending on your use case the dynamic sorting could be a bigger issue than the waterfall calculation.

          • 2. Re: Workaround for a table calculation within a LOD
            Jackson Kibler

            Thanks for your input Stephen, would you mind uploading the workbook again? The current copy errors out whenever I open, won't even let me unpackage.

            Thanks!

            • 3. Re: Workaround for a table calculation within a LOD
              Stephen Rizzo

              Sure, here you go. I can upload a copy compatible with older versions of Tableau if that is the issue.

              • 4. Re: Workaround for a table calculation within a LOD
                Andrew Kim

                Hey Jackson,

                 

                I'm not sure if you're still looking for a solution but I've attached a workbook with my attempt.

                Final.png

                 

                I'm almost certain there's a better way to solve but regardless...

                ***I'm using the Setup worksheet to explain a majority of this:

                Setup.png

                 

                Okay, so I built my solution on top of two indexes that are computed at different levels:

                Category Index computes the index for each Category and ID, restarting every Category. The reason why I want it to compute over Category and ID is important to how I filter the resulting worksheet at the end.

                 

                ID Index helps identify order for the ID values and serves as the basis for the waterfall count. If a value is equal to 1 then it is the first appearance on the worksheet based on the sort order, therefore, every resulting index value greater than 1 indicates that A) the ID is not unique and is contained by another Category and B) we can ignore these values in our Waterfall Count.

                 

                Count is simply the count of each ID contained in each Category regardless of uniqueness.

                 

                Waterfall is the calculation you're after. It's a window sum on an if statement checking whether or not ID Index is equal to 1. It's summing up all of the ID's with the ID Index value of 1 (the first occurrence of the ID).

                 

                My solution required ID to be present on the view because the Waterfall Count uses a combination of table calculations based on the ID field, however, we address this issue by filtering the Category Index field. By filtering on the value 1 we reduce the worksheet down to just one row for each Category giving the appearance that ID is not present on the view.

                 

                That's basically the solution. I can see this getting much more complicated as additional fields get added and it probably won't be the fastest workbook out there.

                 

                If you have any questions I suggest looking through the calculations themselves as well as the table calculation parameters (right click field on view > Edit Table Calculation...), and of course, I can respond to any questions as well.

                 

                The Notes field is just an extra I added for some additional pizzazz but it was a pretty ugly calculation that I know can be simplified so I won't go into details here.

                 

                Thanks!

                 

                - Andrew

                1 of 1 people found this helpful
                • 5. Re: Workaround for a table calculation within a LOD
                  Jackson Kibler

                  That did the trick, Andrew. It was a matter of bringing in the level of detail and using exclude where it wasn't needed, so approaching the problem from the opposite way.

                   

                  Thanks