SUM BETWEEN TO DATES WITH CONDITION

[entsh]

HELLO

please see table1 and table 2

TABLE1:

NAME	START	FINISH	SUM NUMBERS (FOR DATES IN TABLE 2 BETWEEN START AND FINISH IN TABLE1)
A	2020/01/01	2020/01/15	180
B	2020/02/01	2020/02/20	100
C	2020/02/01	2020/02/25	0

TABLE2:

NAME	DATE	NUMBER
A	2020/01/02	50
A	2020/01/02	60
A	2020/01/03	40
A	2020/01/14	30
A	2020/01/16	20
B	2020/02/15	55
B	2020/02/16	45
C	2020/04/05	30

i want to add "sum" field in table1

formula : sum numbers in TABLE2 that DATE between START and FINISH IN TABLE1

WOULD U PLEASE ATTACH QLIKVIEW FILE? (QVW FORMAT)

[JuanGerardo]

Hi @entsh , you can try following expression:

Sum({<DATE = {">=$(=Min(START))<=$(=Max(FINISH))"}>}  NUMBER)

JG

[entsh]

IT DOESNT WORK ! JUST SHOW 0 !

WOULD U PLEASE ATTACH QLIKVIEW FILE? (QVW FORMAT)

[JuanGerardo]

Hi, I have no file, just giving you guidance. Probably it is because of the date format, you can try:

Sum({<DATE = {">='$(=Date(Min(START), 'YYYY/MM/DD'))'<='$(=Date(Max(FINISH)), 'YYYY/MM/DD')'"}>}  NUMBER)

JG

[entsh]

THANKS A LOT BUT IT SHOWS 0 !

[mfchmielowski]

Hi. I'll not give you the qvw file, sorry. You're looking for interval match function. Documentation

Adapt second example usage. 

[MayilVahanan · Accepted solution]

HI @entsh 

PFA

Intevalmatch concept will help you for this requirement.

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD * INLINE [
    NAME, START, FINISH
    A, 2020/01/01, 2020/01/15
    B, 2020/02/01, 2020/02/20
    C, 2020/02/01, 2020/02/25
];

Left Join(tab1)
LOAD * INLINE [
    NAME, DATE, NUMBER
    A, 2020/01/02, 50
    A, 2020/01/02, 60
    A, 2020/01/03, 40
    A, 2020/01/14, 30
    A, 2020/01/16, 20
    B, 2020/02/15, 55
    B, 2020/02/16, 45
    C, 2020/04/05, 30
];

Left Join(tab1)
LOAD NAME, Sum(If(DATE>=START And DATE<=FINISH,NUMBER)) As Sum
Resident tab1
Group By NAME;

[Saravanan_Desingh]

Output: