3 Replies Latest reply on Jun 18, 2019 6:53 AM by Stephen Rizzo

    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