topic Calculating Range from Visit to Order in QlikView

Calculating Range from Visit to Order

Thu, 04 Apr 2013 21:54:26 GMT

Hi,

I would like to get following calculated informations out of the table below:

Visits till first order (4 visits)

Time since first visit till first order ( 3days 1hour) 13.01.01 13:18:00 - 10.01.01 12:12:00

Visits since last order till next order (3 visits)

Time since first visit after last order till next order (2days 6min 1sec) 18.01.01 13:19:11 - 16.01.01 13:13:10

This should go on in a loop.

If after the last visit no order-timestamp exists the script should count till today().

timestamp= dd.mm.yy hh:mm:ss

customer:

johndoe

visits(timestamp)

10.01.01 12:12:00

11.01.01 12:12:11

12.01.01 12:12:12

13.01.01 12:12:13

16.01.01 13:13:10

17.01.01 13:13:12

18.01.01 13:13:11

19.01. 12:00:00

orders(timestamp)

13.01.01 13:18:00

18.01.01 13:19:11

Re: Calculating Range from Visit to Order

swuehl — Thu, 04 Apr 2013 22:31:26 GMT

Maybe like attached? Using this script:

TS:

LOAD TS, 'Orders' as Type INLINE [

13.01.01 13:18:00

18.01.01 13:19:11

];

Let vNumOrders = NoOfRows('TS');

LOAD TS, 'Visits' as Type INLINE [

10.01.01 12:12:00

11.01.01 12:12:11

12.01.01 12:12:12

13.01.01 12:12:13

16.01.01 13:13:10

17.01.01 13:13:12

18.01.01 13:13:11

19.01.01 12:00:00

];

TMP:

LOAD *, if(Type= 'Visits', AutoNumber(TS, OrderNum) ) as VisitNum;

LOAD TS, Type, if(recno() = 1,$(vNumOrders)+1, peek(OrderNum) - if(Type='Orders',1,0)) as OrderNum

Resident TS order by TS desc;

drop table TS;

Result:

LOAD OrderNum,

max(VisitNum) as DeltaVisits,

interval(if(LastValue(Type) <> 'Orders', now(), LastValue(TS)) - FirstValue(TS),'dd hh:mm:ss' ) as DeltaTime ,

if(LastValue(Type) <> 'Orders', true() , false() ) as OrderPending

Resident TMP group by OrderNum order by TS asc;

Re: Calculating Range from Visit to Order

Mon, 29 Apr 2013 07:47:49 GMT

Thank You verry much for this solution and sorry for the late response!

It works well