3 Replies Latest reply on Jan 28, 2016 11:20 AM by Steve Mayer

    Need largest value by customer if duplicate, need value from first end date

    Damara Sinkler Taylor

      Hello,

       

      I am stuck. I thought I had it, but it turns out not fully.

       

      • I need to find the largest value contract for each Customer ID
      • If there are multiple contracts that are the same value, I need to find the contract with the first end date.
      • I only want to show the applicable Customer, Client, End Date and Value in the view

       

      I had used the following calculation in the filters but that doesn't give me the largest, which is the first priority and everything comes up as false:

       

      SUM([Value])=WINDOW_MAX(SUM([Value])) AND

      MIN([End Date])=WINDOW_MIN(MIN([End Date]))

       

      Thank you in advance.

        • 1. Re: Need largest value by customer if duplicate, need value from first end date
          Steve Mayer

          @Shawn - looks like it was 9.2. I could not open with 9.1.

           

          Damara - attached is a possible solution which uses an LOD calculation to find the largest contract for each customer, and a table calculation to find the correct date. The logic in your existing calculation wouldn't work, because it was looking for records that were _both_ the largest contract and the minimum date. Your test data hid the case where you could have a customer with a largest contract that did not fall on the minimum date. I modified your test data so that I could test this use case.

           

          The attached workbook solves this in 3 steps. I always like to start by displaying data unfiltered & then adding the filters once I have the right calculated fields.

           

          Step 1

          Create an LOD calculation (I called it "Is Largest Contract")

           

          [Value] = { FIXED [Customer ID] : MAX([Value]) }

          This will return TRUE for records with the largest contract value for a given customer.

           

          Then create a 2nd calculated field (I called it "Is Minimum Date"

           

          MIN([End Date]) = WINDOW_MIN(MIN([End Date]))

          This will return TRUE for the earliest end date in a partition. Make sure that the partitioning & addressing is set correctly. You'll want to partition by Customer ID, since you want to find the earliest contract.

           

          Step 2

          Add the "Is Largest Contract" calculated field to Filters, and select "True". This will filter the list to only the largest contracts for each customer.

           

          Step 3

          Add the "Is Minimum Date" calculated field to Filters, and select "True". Set the Partition to Customer ID in the Advanced dialog of "Edit Table Calculation..."

           

          There may be easier solutions, but this approach solves your current use case.

           

          -Steve

          1 of 1 people found this helpful
          • 2. Re: Need largest value by customer if duplicate, need value from first end date
            Damara Sinkler Taylor

            Hey @steve. Thank you very much. I just ran in to one issue with this, but it's almost there. I noticed that one client ID that has 3 contracts on the same day, 2 of which are the same value, one of which is actually the highest, still show up. For example:

             

            Client 1 - CT 1 End Date Feb 8 4501

            Client 1 - CT 2 End Date Feb 8 2251

            Client 1 - CT 3 End Date Feb 8 2251

             

            It's not issue if the doubles show up for CT 2 and 3 IF they were actually the highest, but they're not.

             

            I'm not really familiar with LOD calculations, but if I have other filters on the data, does that affect it?

            • 3. Re: Need largest value by customer if duplicate, need value from first end date
              Steve Mayer

              Other filters may be affecting the result. Please post an updated workbook with your problem use case.

               

              One thing to note about the LOD calculation for largest contract - as currently written, it will only be TRUE for the largest contract across the entire dataset, regardless of any filtering on the Viz. That is what the "FIXED [Customer ID] : MAX([Value])" part of the calculation is doing - for every distinct Customer ID in the dataset, find the MAX Value across all records for that Customer ID.