Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Sum in between intervals

Good morning,

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

Sales table:

Sales dateBuyerValue
2016-01-01A1
2016-02-01A2
2016-02-02B3

Interval table:

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

The result:

Sales dateBuyerValueFromToSum
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.

Thank you for your ideas!

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Mindaugas,

try

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

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


Left Join
Load DISTINCT Buyer,[Sales date],
Sum(If([Sales date] >= From and [Sales date] <= To,Value,0)) as Sum
Resident Temp
Group By Buyer,[Sales date];

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

Drop Table
Temp;

Regards,

Antonio

View solution in original post

5 Replies
its_anandrjs

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

Regard

Anand

jonathandienst
Partner - Champion III
Partner - Champion III

Heres' the logic

Sales:

LOAD [Sales date],

  Buyer,

  Value,

  ...

Interval:

LOAD Buyer,

  From,

  To

FROM ...

Left Join (Sales)

IntervalMatch([Sales date], Buyer)

LOAD From, To, Buyer

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
its_anandrjs

Try this way also

Sales_table:
LOAD * Inline [
Sales date,     Buyer,     Value
2016-01-01,     A,   1
2016-02-01,     A,   2
2016-02-02,     B,   3
]
;

Interval_table:
IntervalMatch(Buyer)
LOAD * Inline [
From, To
2016-01-01,     2016-01-05
2016-02-01,     2016-02-05
2016-01-01,     2016-02-29
]
;

Regards

Anand

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

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

antoniotiman
Master III
Master III

Hi Mindaugas,

try

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

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


Left Join
Load DISTINCT Buyer,[Sales date],
Sum(If([Sales date] >= From and [Sales date] <= To,Value,0)) as Sum
Resident Temp
Group By Buyer,[Sales date];

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

Drop Table
Temp;

Regards,

Antonio