1 Reply Latest reply: Apr 22, 2012 9:19 AM by Stefan Wühl RSS

    group round up

       

      Hello all

      Need help anyone has any ideas ???

      my database looks like this

       

      Date time / sale value

      19/4/2012 14:00 - 300

      19/4/2012 14:00 - 300

      19/4/2012 14:00 - 300

      19/4/2012 14:15 - 500

      19/4/2012 14:15 - 500

      19/4/2012 14:15 - 500

       

      I need to sum up each quarter hour and then subtract meaning (1500-900 = 600)

      My problem is that sometimes the DB sends hours like:

       

      19/4/2012 14:01 - 300

      19/4/2012 14:16 – 500

      And then the sum group doesn’t consider these hours with 14:00 , 14:15 how can I round up the group

       

      Thanks alot

       

        • Re: group round up
          Stefan Wühl

          Timestamps have a numerical representation, the integer part is representing days, the decimal places are representing hours, minutes, seconds (as fraction of the day).

           

          So you can do a numerical rounding, like

           

          Timestamp(round(TimeStamp,1/(24*4)))

           

          to create a new timestamp with value rounded to closest quarter of an hour (there are 24*4 quarters per day, so 1/(24*4) is the numerical step we could use here).

           

          But please note that this might not match a timestamp that is created by QV time functions (e.g. using maketime() function). Please refer to http://qlikviewnotes.blogspot.de/2011/10/correct-time-arithmetic.html for a more detailed explanation.

           

          To get around this, you could use

           

          Timestamp(daystart(TimeStamp)+Timestamp#(round(3600*hour(TimeStamp)+60*Minute(TimeStamp)+second(TimeStamp),60*15),'ss'))

           

          I think you can use both methods, but you need to only use one method in your app consistently, so if you try matching timestamps creating with the two different approaches, you might run into trouble.

           

          Please see also attached small sample (which will show also some differences between the two methods).

           

          Hope this helps,

          Stefan