Sum between dates in each row

[nigel987]

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

[sunny_talwar · Accepted solution]

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;

[nigel987]

Hi Sunny,

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

[sunny_talwar]

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