topic Re: Sum in between intervals in QlikView

Sum in between intervals

MindaugasBacius — Wed, 09 Nov 2016 06:22:18 GMT

Good morning,

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

Sales table:

Sales date	Buyer	Value
2016-01-01	A	1
2016-02-01	A	2
2016-02-02	B	3

Interval table:

Buyer	From	To
A	2016-01-01	2016-01-05
A	2016-02-01	2016-02-05
B	2016-01-01	2016-02-29

The result:

Sales date	Buyer	Value	From	To	Sum
2016-01-01	A	1	2016-01-01	2016-01-05	1
2016-02-01	A	2	2016-02-01	2016-02-05	2
2016-02-02	B	3	2016-01-01	2016-02-29	3

In production version the Sales table have few million transactions.

Interval table contains around one thousand rows.

Thank you for your ideas!

Re: Sum in between intervals

its_anandrjs — Wed, 09 Nov 2016 06:48:26 GMT

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

Regard

Anand

Re: Sum in between intervals

jonathandienst — Wed, 09 Nov 2016 07:14:54 GMT

Heres' the logic

Sales:

LOAD [Sales date],

Buyer,

Value,

...

Interval:

LOAD Buyer,

From,

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)

Re: Sum in between intervals

its_anandrjs — Wed, 09 Nov 2016 07:29:23 GMT

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

Re: Sum in between intervals

MindaugasBacius — Wed, 09 Nov 2016 07:37:46 GMT

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

Re: Sum in between intervals

antoniotiman — Wed, 09 Nov 2016 09:26:21 GMT

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