topic Re: Sum difference between two dates in QlikView

Sum difference between two dates

Mon, 24 Sep 2012 21:33:43 GMT

Hi,

I have two date fields that I need to do a diff on. What I simply do right now is date1 - date2 and format the column as "interval". If I want to sum all the differences I don't get the correct numbers (the diff gives me data such as 0.1239839498). Please help!

Thanks

Shilpa

Re: Sum difference between two dates

Mon, 24 Sep 2012 21:50:23 GMT

It sounds like your dates are in a timestamp format. Try adding Date(DateField,'MM/DD/YYYY') to adjust your formatting.

Re: Sum difference between two dates

Mon, 24 Sep 2012 21:51:18 GMT

Well, I do need the time stamp format since I am counting the difference in hours and also minutes.

Re: Sum difference between two dates

Mon, 24 Sep 2012 21:57:04 GMT

Just adjust the format. For example:

Interval(Timestamp(activity_totime,'MM/DD/YYYY HH:MM')-Timestamp(activity_fromtime,'MM/DD/YYYY HH:MM'))

Then set Chart Properties > Number > Interval

Re: Sum difference between two dates

Mon, 24 Sep 2012 22:04:15 GMT

Thanks! I am already able to get the difference. However, now I want to sum it up. And when I call "=sum("Time_taken") - it gives me some wierd numbers. I want to be able to sum up all the intervals to get the total time taken

Re: Sum difference between two dates

MayilVahanan — Mon, 24 Sep 2012 22:38:51 GMT

Try like this

=Sum(total [Time Taken])

Hope that helps

Re: Sum difference between two dates

Mon, 24 Sep 2012 23:09:34 GMT

Thanks! That seems to be working., However, looks like I need to do the sum on latest data only - so I need a aggr(max(start_time), test_id) - something like that. I will try that out. Thanks for your help!