topic Re: Sum between dates in each row in QlikView

Sum between dates in each row

nigel987 — Tue, 24 Apr 2018 14:31:37 GMT

Hi,

I have the number of views which I would like to sum, but only for the dates where a product was active (see attached sample file).

Events:

LOAD * INLINE [

UserID, Date, Views

1, 01.01.2018, 1

1, 02.01.2018, 2

1, 03.01.2018, 3

1, 07.01.2018, 10

1, 08.01.2018, 20

1, 09.01.2018, 30

];

Products:

LOAD * INLINE [

UserID, ProductID, ProductStart,ProductEnd

1, 1, 01.01.2018, 03.01.2018

1, 2, 07.01.2018, 09.01.2018

];

In my result table I would expect to see the value 6 for ProductID = 1 and 60 for ProductID = 2.

Can anyone help me with that? I found out that the chart expression is evaluated beforehand and thus not possible to compute this in a simple way, but does anyone know a workaround?

Many thanks in advance

Re: Sum between dates in each row

sunny_talwar — Tue, 24 Apr 2018 14:36:55 GMT

Try interval match

Events:

LOAD * INLINE [

UserID, Date, Views

1, 01.01.2018, 1

1, 02.01.2018, 2

1, 03.01.2018, 3

1, 07.01.2018, 10

1, 08.01.2018, 20

1, 09.01.2018, 30

];

Products:

LOAD * INLINE [

UserID, ProductID, ProductStart,ProductEnd

1, 1, 01.01.2018, 03.01.2018

1, 2, 07.01.2018, 09.01.2018

];

Left Join (Events)

IntervalMatch(Date, UserID)

LOAD ProductStart,

ProductEnd,

UserID

Resident Products;

Re: Sum between dates in each row

nigel987 — Tue, 24 Apr 2018 14:43:04 GMT

Hi Sunny,

thanks. I was reading IntervalMatch yesterday and this seems to be the same approach. Thanks!

Re: Sum between dates in each row

sunny_talwar — Tue, 24 Apr 2018 14:45:11 GMT

Yes, slight difference is that this is IntervalMatch (Extended Syntax)