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

# 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

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.