topic Re: SUM BETWEEN TO DATES WITH CONDITION in QlikView

SUM BETWEEN TO DATES WITH CONDITION

entsh — Wed, 12 May 2021 19:21:30 GMT

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)

Re: SUM BETWEEN TO DATES WITH CONDITION

JuanGerardo — Wed, 12 May 2021 19:01:45 GMT

Hi @entsh , you can try following expression:

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

Re: SUM BETWEEN TO DATES WITH CONDITION

entsh — Wed, 12 May 2021 19:18:56 GMT

IT DOESNT WORK ! JUST SHOW 0 !

WOULD U PLEASE ATTACH QLIKVIEW FILE? (QVW FORMAT)

Re: SUM BETWEEN TO DATES WITH CONDITION

JuanGerardo — Wed, 12 May 2021 19:25:53 GMT

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)

Re: SUM BETWEEN TO DATES WITH CONDITION

entsh — Wed, 12 May 2021 19:41:04 GMT

THANKS A LOT BUT IT SHOWS 0 !

Re: SUM BETWEEN TO DATES WITH CONDITION

mfchmielowski — Wed, 12 May 2021 20:49:02 GMT

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

Adapt second example usage.

Re: SUM BETWEEN TO DATES WITH CONDITION

MayilVahanan — Thu, 13 May 2021 00:32:55 GMT

HI @entsh

PFA

Intevalmatch concept will help you for this requirement.

Re: SUM BETWEEN TO DATES WITH CONDITION

Saravanan_Desingh — Thu, 13 May 2021 01:35:47 GMT

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;

Re: SUM BETWEEN TO DATES WITH CONDITION

Saravanan_Desingh — Thu, 13 May 2021 01:36:20 GMT

Output: