Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Reply
swuehl
MVP
MVP

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