4 Replies Latest reply on Nov 14, 2018 6:12 PM by swaroop.gantela

    LOD lookup or just help with my lookup

    Justin Pulley
      Id
      spot
      estimated completionpublished date
      1abc1/1/20209/1/2018
      2abc1/1/202010/1/2018
      3abc2/1/202010/15/2018
      4abc2/1/202010/30/2018

      5

      abc3/1/202011/5/2018
      6def1/1/20209/1/2018
      7def1/1/202011/5/2018
      8hij2/1/202010/1/2018
      9hij2/1/202010/15/2018
      10hij3/1/202011/5/2018

       

       

      Okay I have a requirement to provide a dashboad that shows the following

       

       

      Spot
      Previous Completion Estimate (1 before current)
      Current Completion Estimate
      Number of times the Estimated Completion changed
      Number of Days changed between previous and last
      abc2/1/20203/1/2020330
      def1/1/20201/1/20200No Change
      hij2/1/20203/1/2020130

       

      I am having a heck of a time getting lookup(max([current]),-1) to work.  is that not the right way to do this?

        • 1. Re: LOD lookup or just help with my lookup
          Justin Pulley

          Sorry I should have said for Previous Completion Estimate I am using LOOKUP(MAX([current]),-1)

           

          Also publish date messes up the dashboard if I put it on detail or anywhere else on the viz, so that is what is making me not understand how ti get what I need.

          • 2. Re: LOD lookup or just help with my lookup
            Tushar  More

            Please check the attached workbook and let me know what you think.

            • 3. Re: LOD lookup or just help with my lookup
              Justin Pulley

              My limited data set has made the issue unclear, I posted when I was working on this for some time.  The date isn't always the max date.

               

              1abc1/1/20209/1/2018
              2abc1/1/202010/1/2018
              3abc2/1/202010/15/2018
              4abc2/1/202010/30/2018

              5

              abc3/1/202011/5/2018
              1def1/1/20209/1/2018
              2def1/1/202011/5/2018
              1hij2/1/202010/1/2018
              2hij2/1/202010/15/2018
              3hij3/1/202011/5/2018
              6abc2/1/202011/10/2018
              7abc1/15/202011/14/2018
              3def11/15/201911/10/2018
              4def11/14/201811/14/2018
              4hij2/15/201811/10/2018

               

              The actual data has dates, these estimated completion dates are not always min or max, but are the last published estimated completion.  That is why I am struggling with this.  It feels like it should be a lookup for the final row value per spot and the final row value minus 1 per spot.  So far I can't get that to work.

               

              The only thing that is always the highest is the ID and the published date as they relate to spot.

              • 4. Re: LOD lookup or just help with my lookup
                swaroop.gantela

                Justin,

                 

                I think you can avoid the use of lookups in this case by instead using a combination of

                LOD max and LOD min difference from the max.

                 

                The Most Current Completion Estimate Date is:

                { FIXED [Spot]:MAX(

                IF [Published Date]={ FIXED [Spot]:MAX([Published Date])}

                THEN [Estimated Complete] END)}

                 

                The difference between published date and max published date:

                DATEDIFF('day',[Published Date],{ FIXED [Spot]:MAX([Published Date])})

                 

                Previous Estimate Date(least difference in published date from the max):

                { FIXED [Spot]:MIN(

                IF [Difference to Max Published Date]=

                { FIXED [Spot]:MIN(

                IF [Difference to Max Published Date]>0 THEN [Difference to Max Published Date] END)}

                THEN [Estimated Complete] END)}

                 

                 

                The number of times the estimate changed per spot is :

                { FIXED [Spot]: COUNTD([Estimated Complete])}

                 

                Please see workbook v10.3 attached in the Forum Thread:

                LOD lookup or just help with my lookup