topic Date calculation in QlikView

Date calculation

Wed, 25 Nov 2009 01:33:27 GMT

Hi all

I have a transaction file with customer and date (with a date format).

I would like to build a new file where for each customer I calculate duration between 2 dates

For example

- The first 2,

- The first & the last,

- The 2 last

- or any 2 dates

Maybe you have good solution for my problem

Jean-Jacques

Date calculation

johnw — Wed, 25 Nov 2009 02:56:13 GMT

If I understood the question:

LOAD
Customer
,min(Date,2)-min(Date) as "days between the first two dates"
,max(Date) -min(Date) as "days between the first date and last date"
,max(Date) -max(Date,2) as "days between the last two dates"
,some other expression for whatever two dates you wanted as "days between some other two dates"
RESIDENT [Customer and Date Table]
GROUP BY Customer
;

Date calculation

Wed, 25 Nov 2009 03:33:38 GMT

Yes, you understood well.

Do I need to order the file before ?

Date calculation

johnw — Wed, 25 Nov 2009 03:43:38 GMT

It is not necessary to sort the file, if that's what you are asking. The min() and max() functions don't require the data to be in order.

Date calculation

Wed, 25 Nov 2009 04:12:48 GMT

Tx john