Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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