4 Replies Latest reply on Apr 5, 2018 7:10 AM by Zach Ferris

    Level of Detail filtering

    Zach Ferris

      Here is a list of Invoice Numbers, Payment reasons, and Balance Amounts. I would like to manipulate only the invoice numbers with multiple payment reasons. I created

       

      {FIXED [Inv Nbr Display] : COUNTD([Payment Reason1])} > 1

       

      To show only results with more than one reason. So far so good. Here we can see that, instead of dividing up the Balances appropriately amongst the reasons, it often blankets the total across all Payment Reasons. I only want to see multiple Payment Reasons when the balance amounts are not the same for a given invoice (For example: Inv Nbr Display = 2419474 shows 3 Payment Reasons, 1 for $147.10 and 2 for $73.55). Is that possible? Nesting an LOD function perhaps? To just isolate those with multiple values?

       

      EXPOSITION:

      The reason why I am looking into this is that the original book was done through data blends. It took a long time to open and didn't give any LOD for the 1-to-many relationship, but a Join is more efficient and answers the 1-to-many scenario. BUT, in doing so, this skews our Billing workbook because a Balance of $1,400.00 for one invoice number with 3 Payment Reasons now adds 2 additional $1,400.00s across 2 other reasons (whereas before it was counted once and counted in the asterisk).

       

      In order to counteract this, I have a much more complicated question. A question that is more opinion rather than execution (but still requires help with the calculation). I would like to come up with a way to "pick one" Payment Reason. I thought I might make a Set of the top 10 Payment Reasons by balance, but the set didn't work. I did create a group, however:

       

      If [Isolates Multiple Payment Reasons] = TRUE

      THEN [Top Ten Reasons] ELSEIF

      [Isolates Multiple Payment Reasons] = False

      Then NULL END

       

      (The reason I prefer Sets to Groups is that the Sets would be dynamic and could change based on the condition of Top 10 SUM([Bal Amt]))

       

      This picks a top ten reason, when provided, but does not pick a reason if multiple top ten reasons occur. There is also the obvious flaw of "Null"-ing out results that are not in the top ten. Does anyone know why my set didn't work? Does this sound like valid logic? Applying the Balance only to the most common or most costly Payment Reason as to not inflate our numbers with duplicates? Is it possible to Sort the Payment Reasons in Descending Order (by COUNTD([Inv Nbr Display]) or SUM([Bal Amt]) and have the IF/ELSE or Case scenario pick the Payment Reason that is highest up? Perhaps Sort the Payment Reasons by SUM([Bal Amt]) and then keep the first Payment Reason? Does this require some of the Tableau Python environment? I am sure it can be done that way, but that is my last case scenario. My only other thoughts are to do the Blend to keep the asterisk and use the Join to be a separate Sheet that allows for greater Level of Detail when they see the asterisk but I worry that I'm back where I started in creating a slower workbook; keep the blend and duplicated data and inform the team that this is occurring so the balance amounts might look inflated but they now have a fast, fully-functional workbook; or try and create a method described in this post - a method to systematically pick better Payment Reasons.

       

      I really hope this makes sense. I think it does, but it's because I've been working with this dirty data/dirty reporting for some time.

        • 1. Re: Level of Detail filtering
          Jim Dehner

          Zach

          please see the attached - I was able to break the data into Review and No Review - - i

           

           

          this determines the fixed balance

           

          this is the filter that ID's those invoices to review

           

          you can place it directly on the filter shelf and set to Review

           

          Or you can use it to create a set

           

           

          either way you get this

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Level of Detail filtering
            Ivan Young

            Hi Zach,

            I'm not really sure what your end goal is.  Not double counting the duplicate bal amts brought in by multiple payment reasons isn't too difficult and isolating invoices like 2419474 is also doable.

             

            I'm not entirely clear on what you are trying to do with the sets, an invoice can have multiple payment reasons within the top 10 or no payment reasons within the top 10 so that wouldn't return a single payment reason per invoice.  Invoice 3036416 has 3 payment reasons in the top 10.  Below are some formulas that might help you get where you are trying to go.

             

            Regards,

            Ivan

             

            To create measure that won't double count your payment reasons is { FIXED [Inv Nbr Display], [Bal Amt] : MIN([Bal Amt]) }

             

            To isolate invoices with multiple payment reasons and balance amounts add this filter: { FIXED [Inv Nbr Display]  : COUNTD([Bal Amt])} > 1

            • 3. Re: Level of Detail filtering
              Zach Ferris

              I ALWAYS forget about doubling up dimensions in a LOD Function!! However, the multiple Balances did not seem to work. My goal is to create a calculation where

               

              IF

              {FIXED [ Inv Nbr Display] : COUNTD [Payment Reason1]} = 1

              Then

              [Payment Reason1]

               

              // because I don't want to lose all Payment Reasons that only have one

               

              ELSE IF

               

              //Something that breaks up the 1-to-many circumstance. Obviously, if there are two payment reasons with two separate balances, our job is done. However, if there is an example like I mentioned above we must keep exactly 1 payment reason per distinct balance amount. I mentioned Sets because, what made the most sense was to pick the most common or expensive Payment Reason. It was simply an effort to prioritize what Reason is shown. And the third case (although similar to what was just said) is where there are >1 Payment Reasons with == Bal Amts and we must show, just one.

               

              END

               

              I assume there will be three statements in an IF scenario or three WHENs in a CASE scenario.

               

              1) If there exists exactly one Payment Reason then print it.

              2) If there exists multiple balances then print them.

              3) If there are multiple payment reasons of equal balance amount connected to a FIXED invoice then print only one Payment Reason. (Preferably the printed one would be a more common payment reason as to not inflate less common payment reasons.

              • 4. Re: Level of Detail filtering
                Zach Ferris

                Look up for hopefully more clarity about my query. I don't think I can reply to both of you, simultaneously. However, HOW DID I NOT EVER THINK TO NEST IF/ELSE INTO LODS! That's awesome. And it totally worked on isolating those with different balances.

                 

                Now I need a method to filter down to just 1 payment reason by Fixed Inv Nbr and Bal Amt. I think that might require a min(payment reason)? Just saying, "filter down to 1" made me think that way.

                 

                The biggest problem will be putting all of these cases/if+else together. I tried a IF/ELSE calculation to keep all Payment Reasons when there is 1 per invoice ELSEIF Different Way = Review then display those, END. And I was told that IF/ELSE wants Strings and not Boolean. So that might influence how these are written.