group round up

Anonymous — Sun, 22 Apr 2012 08:33:38 GMT

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: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

swuehl — Sun, 22 Apr 2012 13:19:46 GMT

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

topic Re: group round up in QlikView

group round up

Re: group round up