2 Replies Latest reply on Dec 3, 2012 9:08 AM by Tom Hewett

    Count number of days based on non-null values in a measure

    Tom Hewett

      All,

       

      I am trying to compare production values through time for different wells, so that we can compare their profiles.  The problem is that different wells are drilled at different times, so just using the date field shows the profiles, but separated (see attached .twbx).  What I'd like is to be able to create dimension called 'Days' and assign 1 to the earliest day that a well had a production value associated, and increment up by 1 each day from there.  This would allow me to put Days  on the Columns shelf and overlay the production profiles (so in the attached workbook, the blue profile for Well 1 would shift to the left to overlay on the orange profile of well 2). 

       

      I'm sure there's a way to do this - and probably much easier than what I've tried so far - but I can't quite figure it out and didn't see a similar question in a scan of the forums.

       

      Thanks in advance for your help!

       

      Tom

        • 1. Re: Count number of days based on non-null values in a measure
          Tracy Rodgers

          Hi Tom,

           

          One way that this can be done is to create a calculated field similar to the following and use it in place of Date on the columns shelf:

           

          if Well=2 then Date elseif Well=1 then dateadd('day', -11, Date) end

           

          Hope this helps a bit!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Count number of days based on non-null values in a measure
            Tom Hewett

            Thanks for the suggestion Tracy.  That does work in this case, but what I really was looking for is a way to do this dynamically, with more than 2 wells (I apologize - I should have specified that originally).

             

            In your example calculation, you looked at the data and saw that the offset in start dates of the wells was 11 days, so you hard coded that value.  Is there a way to write the calculation to set that offset value for each well based on the first date that contains a production value (maybe by setting a parameter)? Obviously, it would be easier if my database included another dimension to specify this start date (and I'm working with IT to add it to the database), but until then, I'm hoping you guys can help me out with a hack.

             

            Thanks again!