6 Replies Latest reply on Dec 29, 2016 1:37 PM by Supriya Salunkhe

    LOD for row level

    Supriya Salunkhe

      Hello all,

       

      I need to tweak my LOD to get rid of the rows which have $0 or less payments. I need to then add the remaining positive values.
      I need to include the dimension LossNumber+Coverage in the LOD but not display it.

       

      I created a LOD expression, something like this :

      { INCLUDE [LossNumber+Claimant+Coverage (copy)] : MIN([Payment Amount])} >1

       

      So basically get an LOD to add payment amounts only if the individual payment >0

        • 1. Re: LOD for row level
          Norbert Maijoor

          Hi Supriya,

           

          Would the following be an option.

           

          Rightclick Sum[Payment Amount] > filter set At least 1

           

          • 2. Re: LOD for row level
            jason.harmer

            I'd also suggest setting up a t/f field so that it marks anything with a payment >0 (i.e. only positive) as true, and filter to only show true values.

            • 3. Re: LOD for row level
              Bobby Souza

              Hi Supriya,

               

              I believe this can be done by using {FIXED}. However, it would be much easier if you could attach a packaged workbook containing the view shown above. Also, what's your end goal in displaying only those payments greater than $0? Knowing this context can help determine the details of our LOD or other possible routes for accomplishing your goal.

               

              From what I gather though, given the info above, it seems you can create a calculated field:

               

              IF {FIXED [LossNumber+Claimant+Coverage (copy)],[Payment Date]: SUM([Payment Amount])}>0 THEN 1 ELSE 0 END

               

              Note: I'm assuming the LossNumber+Coverage payments occur multiple times within a year which is why Payment Date is included in the FIXED expression.  However, if it's possible for multiple LossNumber+Coverage payments to occur during the same month/year then you need to find another field that will distinctly show each transaction (some type of individual ID field is preferable).

               

              After you've created your LOD, drag this field into the filter and select 1. This will give you those individual payments greater than $0. However, to reiterate, that's only if multiple payments from the same LossNumber+Coverage dimension during the same Payment Date does not occur. If it does occur then you'll have to bring another ID field (or something that distinguishes between each transaction) into the LOD. This is important because without a way of truly separating each individual payment/transaction, Tableau will aggregate these numbers automatically when bringing the pills into your view.

               

              I'd love to help out more but I'd need a bit more context.

               

              Hope this helps - cheers!

              • 4. Re: LOD for row level
                Bora Beran

                { INCLUDE [LossNumber+Claimant+Coverage (copy)] : MIN([Payment Amount])}

                 

                is a measure so it will work as a measure filter and work after sums are computed (sum first then filter).

                 

                You can do this instead and add it to filter shelf like Norbert suggested

                 

                {Fixed [Month,year of payment data],[LossNumber+Claimant+Coverage (copy)] : MIN([Payment Amount])}

                 

                Fixed LOD expressions can be treated as dimension filters which means they can happen before sums are computed (filter first then sum).

                 

                After this if you compute sum([payment amount]) without the [LossNumber+Claimant+Coverage (copy)] dimension in the view, it will give you the sum for only the positive payments.

                 

                Alternatively you can write the calculation in the following way

                 

                SUM(IIF({ INCLUDE [LossNumber+Claimant+Coverage (copy)] : MIN([Payment Amount])} > 0, [Payment Amount], 0))

                 

                What this would do is to count negatives as 0 towards the sum (so no impact on the sum) and count positive values as their actual Payment Amounts so you would be summing up only the non-negative values which I think is what you want.

                • 5. Re: LOD for row level
                  jason.harmer

                  You can probably modify that to a simpler statement which doesn't take as long to calculate across the table:

                   

                  {FIXED [LossNumber+Claimant+Coverage (copy)],[Payment Date]: SUM([Payment Amount])}>0

                   

                  the statement above will automatically identify itself as a true/false dimension that can be put into the filter shelf and select true as the value.

                  • 6. Re: LOD for row level
                    Supriya Salunkhe

                    Hey everyone,

                     

                    I really appreciate the time you took to answer my question. I wanted to include the [Loss number+Covergae] in the LOD but remove it from the worksheet.
                    It did not give me the desired value when used the filter [payment Amount]>1

                     

                    Bora Beran's answer helped with just a little change.

                    Thanks lot everyone.