Generate sum of a filed for specific date range

[rkspareek1992]

Hi,

I have two tables:

In first table I have Rate by date.. Means different rate for different dates.

for Ex.

Date	Rate
4/15/2017	80
4/16/2017	85
4/17/2017	70
4/18/2017	74
4/19/2017	71

In second table I have primary key which have from and to dates.

Key	From Date	To Date
1	4/15/2017	4/19/2017
2	4/16/2017	4/18/2017
3	4/17/2017	5/20/2017
4	4/18/2017	4/18/2017
5	4/19/2017	5/1/2017

I wants to generate sum of rates for defined date ranges.

Here I just mentioned just only 5 records, in original table I have records in lacs and wants to put sum in from of Key as per desired output.

Desired Output:

Key	From Date	To Date	Sum(Rate)
1	4/15/2017	4/19/2017	380
2	4/16/2017	4/18/2017	229
3	4/17/2017	5/20/2017	Sum of Rate from 17-Apr to 20-May
4	4/18/2017	4/18/2017	74
5	4/19/2017	05-01-17	Sum of Rate from 19-Apr to 1-May

Kindly suggest.

Thanks in advance.

[antoniotiman · Accepted solution]

Hi Rakesh,

maybe

LOAD Date,
Rate
FROM
"https://community.qlik.com/message/1263180"
(html, codepage is 1252, embedded labels, table is @1);
LOAD Key,
[From Date],
[To Date]
FROM
"https://community.qlik.com/message/1263180"
(html, codepage is 1252, embedded labels, table is @2);

Expression :

Sum(If(Date >= [From Date] and Date <= [To Date],Rate))

Regards,

Antonio

[rkspareek1992]

Thanks...