topic Calculate the difference between 2 dates in QlikView

Calculate the difference between 2 dates

Mon, 18 Apr 2011 21:05:04 GMT

Dears,

I've another problem.
On the column A of my spreadsheet I have the Inicial Date values (Value in date format) and in the column B of my spreadhsheet I have the Final Date values (Value in date format) too.

I would like to calculate the difference between this 2 dates in a Text Object.

I tried to do the expression bellow, but it didn't work.

="[Initial Date]-[Final Date]"

What should I do?
Can you help me?

Thanks again for your attention

Calculate the difference between 2 dates

Mon, 18 Apr 2011 21:26:12 GMT

Well there are a few problems here...

If you have one initial date and one final date selected =[Initial Date]-[Final Date] will return a value, but if you don't it won't return anything. If you have multiple dates selected (or nothing selected) you need to use dimensions so use a chart or use aggr in the text object (which isn't what you want in this case).

Also, I don't know what the quotes are for your text object... just do =[Initial Date]-[Final Date].

So you either need to use a chart with one of those dates as the dimension or select only one [Initial Date] and only one [Final Date] if you really want to put it in a text object (or you could use set analysis and variables but you didn't give any indication that that would solve your problem).

Calculate the difference between 2 dates

Tue, 19 Apr 2011 17:10:17 GMT

What I wanna do is to show the average difference between the Initial date and the Final date.

Follow attached is an example of what I wanna do.

Thanks

Calculate the difference between 2 dates

chris_johnson — Tue, 19 Apr 2011 17:19:55 GMT

As a quick answer (not looking at your example), I guess you need to do as Trent has suggested and use the average function:

=avg([Final Date]-[Initial Date])

Regards,

Chris

Calculate the difference between 2 dates

christian77 — Tue, 19 Apr 2011 17:29:00 GMT

Hi Kellybastos:

It´s not possible to see it in a text box at least you select only one record.

Do [Final Date]-[Initial Date] anywhere and you´ll have that difference in days. Check the script, it´s done there. Check the table, both are the same result. Before or after, but remember, in a text box: which record to operate or to show?

Ok.

Calculate the difference between 2 dates

Tue, 19 Apr 2011 17:33:47 GMT

Thanks for your support.

But I need to show the AVERAGE time (in days) to finish the service.

So, in your example, I want to show the average of the ListBox Dif.

How can I do it?

Thanks

Calculate the difference between 2 dates

christian77 — Tue, 19 Apr 2011 17:35:25 GMT

Download it again. I have corrected.

Calculate the difference between 2 dates

Tue, 19 Apr 2011 17:44:33 GMT

Greate Christian. It's working perfectly

This FORUM is the best, ever!