topic Re: How to calculate sum of last two months in script in QlikView

How to calculate sum of last two months in script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

If I have one more dimension (employee), how to calculate sum of two last month for each employee?

Tab:

LOAD * Inline [

Data , Month, Employee

2, 1, a

2, 1,b

4, 2,a

5, 2,c

1, 3,a

2, 4,b

];

Thanks,

Tu Nguyen

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 08:20:49 GMT

May be this

Tab:
LOAD * Inline [
Data , Month, Employee
2, 1, a
2, 1,b
4, 2,a
5, 2,c
1, 3,a
2, 4,b
];
Left Join (Tab)
LOAD Employee,RangeSum(FirstSortedValue(Data,-Month),FirstSortedValue(Data,-Month,2)) as SumMonth
Resident Tab
Group by Employee;

Re: How to calculate sum of last two months in script

Anonymous — Tue, 28 Nov 2017 08:53:39 GMT

Thank you.

It work useful for that case.

I have another question.

I have sample data as below, so the question is in 3 previous days having sales or not?

Data2:

Load * Inline [

month, sales

1, 1

2, 1

4, 0

5, 0

7, 1

9, 1

10, 1];

And the expected result as image:

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 09:58:25 GMT

Why month 8 sales 1 e months 9/10 sales 0 in Final Table ?

Re: How to calculate sum of last two months in script

Anonymous — Tue, 28 Nov 2017 10:03:11 GMT

Sorry, I miss data. The data load should be:

Data2:

Load * Inline [

month, sales

1, 1

2, 1

3, 0

4, 0

5, 0

6, 0

7, 1

8, 1

9, 0

10, 0];

Thank you.

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 10:16:45 GMT

First sample data

Data2:
Load * Inline [
month, sales
1, 1
2, 1
4, 0
5, 0
7, 1
9, 1
10, 1];
Temp:
Noconcatenate
LOAD sales,month,Alt(Peek(month),month) as M1
Resident Data2
Order By month desc;
Drop Table Data2;
Table:
Concatenate
LOAD 0 as sales,month+IterNo()as month
Resident Temp
While month+IterNo() < M1;
Left Join (Temp)
LOAD month,If(RangeSum(sales,Previous(sales),Previous(Previous(sales))) > 0,1,0) as HavingSales_3_previous_month
Resident Temp
Order By month;

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 10:21:22 GMT

2nd data sample (doesn't need to generate missing data)

Data2:
Load * Inline [
month, sales
1, 1
2, 1
3, 0
4, 0
5, 0
6, 0
7, 1
8, 1
9, 0
10, 0];
Left Join (Data2)
LOAD month,If(RangeSum(sales,Previous(sales),Previous(Previous(sales))) > 0,1,0) as HavingSales_3_previous_month
Resident Data2
Order By month;

Re: How to calculate sum of last two months in script

Anonymous — Tue, 28 Nov 2017 10:25:13 GMT

It's great! It's helpful for me.

I have 2 question on this:

- If I apply this for a large table (about 500.000 rows), does it work well?

- If I have one more dimension ( employee - about 300.000 employees) and field [month] now is [date] ( data for 3 month, so about 90 date) => total row is 27.000.000 rows, does it work well?

Thank you so much.

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 10:38:21 GMT

Order By in Script (Load, no Select from DB) is very slow.

Re: How to calculate sum of last two months in script

Anonymous — Tue, 28 Nov 2017 10:45:50 GMT

So is there any another way or solution for this?

Re: How to calculate sum of last two months in script

antoniotiman — Tue, 28 Nov 2017 11:05:25 GMT

Table loaded already sorted.

However, in my script Order By of large table (> 30M rows) lasts 10/15 minutes.

Re: How to calculate sum of last two months in script

Anonymous — Mon, 04 Dec 2017 07:51:29 GMT

Dear Antonio,

Thank you so much.

And kindly need your help on my problem in case having 1 more dimension.

Data:

Load * Inline [

date, sales, outlet

1/1/2017, 1, a

2/1/2017, 1, a

4/1/2017, 0, a

5/1/2017, 0, a

7/1/2017, 1, a

9/1/2017, 1, a

10/1/2017, 1, a

1/1/2017, 0, b

2/1/2017, 1, b

3/1/2017, 1, b

4/1/2017, 1, b

5/1/2017, 0, b

6/1/2017, 0, b

3/1/2017, 0, c

4/1/2017, 1, c

5/1/2017, 1, c

6/1/2017, 0, c

7/1/2017, 0, c

];

Re: How to calculate sum of last two months in script

antoniotiman — Mon, 04 Dec 2017 08:29:45 GMT

I don't understabd Your request,

Which is Your expected result ?

Re: How to calculate sum of last two months in script

Anonymous — Wed, 13 Dec 2017 04:58:54 GMT

Thank you. I found solution, I run inline for every current date.

Thank you so much.