Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist III

## Sum in between intervals

Good morning,

I would like to have sum of sales in between given intervals:

Sales table:

2016-01-01A1
2016-02-01A2
2016-02-02B3

Interval table:

A2016-01-012016-01-05
A2016-02-012016-02-05
B2016-01-012016-02-29

The result:

2016-01-01A12016-01-012016-01-051
2016-02-01A22016-02-012016-02-052
2016-02-02B32016-01-012016-02-293

In production version the Sales table have few million transactions.

Interval table contains around one thousand rows.

1 Solution

Accepted Solutions
Master III

Hi Mindaugas,

try

Temp:
Value
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @1);

Left Join
From,
To
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @2);

Left Join
Sum(If([Sales date] >= From and [Sales date] <= To,Value,0)) as Sum
Resident Temp

NoConcatenate
Resident Temp
Where [Sales date] >= From and [Sales date] <= To;

Drop Table
Temp;

Regards,

Antonio

5 Replies
MVP

Use interval match or did you want the logic how to use  interval match

Regard

Anand

Partner - Champion III

Heres' the logic

Sales:

Value,

...

Interval:

From,

To

FROM ...

Left Join (Sales)

Resident Interval;

DROP Table Interval;

Then create a straight table with dimensions: [Sales date], Buyer, From, To

And expression: Sum(Value)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP

Try this way also

Sales_table:
2016-01-01,     A,   1
2016-02-01,     A,   2
2016-02-02,     B,   3
]
;

Interval_table:
From, To
2016-01-01,     2016-01-05
2016-02-01,     2016-02-05
2016-01-01,     2016-02-29
]
;

Regards

Anand

Partner - Specialist III
Author

I didn't mentioned that I do need sum in the script.

Master III

Hi Mindaugas,

try

Temp:
Value
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @1);

Left Join
From,
To
FROM
"https://community.qlik.com/message/1156621"
(html, codepage is 1252, embedded labels, table is @2);

Left Join
Sum(If([Sales date] >= From and [Sales date] <= To,Value,0)) as Sum
Resident Temp

NoConcatenate