5 Replies Latest reply: May 10, 2011 11:04 AM by Ricardo Ramos RSS

    Difference between dates in Days

    Thom Mumaw

      Been searching the forum trying different things howerver nothing seems to work.  I have 2 dates created on (YYYY-MM-DD) and Resolve On (YYYY-MM-DD).  I need to find out how many days between these dates, but only do it were status is equal to '5'.  Thought I should be able to do this with set analysis, but having no luck.

      Thanks
      Thom

        • Difference between dates in Days
          Oleg Troyansky

          Whether or not you can dod it with Set Analysis, depends on your chart dimensions. Since Set Analysis condition is evaluated only once per chart, you can't use it if your dimension is linked somehow to the two dates that you need to compare to.

           

          My recommendation would be to calculate this information in the script, if possible, to move the heavy load away from the run-time. If your data is small, on the other hand, then you can use an IF statement instead of set Analysis, but keep in mind that this solution won't be scalable and won't perform on larger data sets.

           

          cheers,

           

          Oleg

            • Re: Difference between dates in Days
              John Witherspoon

              Script vs. if() depends on whether you want the numbers to stay the same or change as you make selections.  Like if the dates are two years apart, but other selections can make it so that you're only interested in one month of those two years, do you want to count days in the month or days in the two years?  If the month, use if().  If two years, do it in script.

               

              I think the if() would look something like this, assuming you have a calendar with a Date field.

               

              count({<Status={'5'}>} distinct if(Date>=CreatedOn and Date<=ResolvedOn,Date)))

               

              A more complicated option, if you want the results of the count(if()) instead of a fixed script solution, but need it to perform, is probably to handle it in the data model.  I suppose that's still a script solution but different than actually counting in the script.  Something like this, maybe, though it's hard to guess without knowing more about your data model.

               

              Data:
              ID, CreatedOn, ResolvedOn
              A, January 4, January 7
              B, January 5, January 6

               

              NewTable:
              ID, Date
              A, January 4
              A, January 5
              A, January 6
              A, January 7
              B, January 5
              B, January 6

               

              count({<Status={'5'}>} distinct Date)

            • Re: Difference between dates in Days
              Ricardo Ramos

              I'd suggest the use of the function networkdays(), i'm assuming that you want to get the weekends out...