topic Re: Rolling 12 month sum using above() in multi dimensional pivot table in QlikView

Rolling 12 month sum using above() in multi dimensional pivot table

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

Hello, I have the below table for which I was initially trying to calculate rolling 12 month sum of sales and display at month level.

Month	grade	sale
1	A	10
1	B	20
1	C	30
2	A	10
2	B	20
2	C	30
3	A	10
3	B	20
3	C	30
4	A	10
4	B	20
4	C	30
5	A	10
5	B	20
5	C	30
6	A	10
6	B	20
6	C	30
7	A	10
7	B	20
7	C	30
8	A	10
8	B	20
8	C	30
9	A	10
9	B	20
9	C	30
10	A	10
10	B	20
10	C	30
11	A	10
11	B	20
11	C	30
12	A	10
12	B	20
12	C	30

Initial output: I got this part correct.

Month	sum(aggr(rangesum(above( total sum({<Month=>}sale),0,12)),Month))
1	60
2	120
3	180
4	240
5	300
6	360
7	420
8	480
9	540
10	600
11	660
12	720

But when I pull the second dimension into the pivot table, I am not seeing expected results for Rolling 12 months values for the combination of these dimensions.

Month	grade	sum(aggr(rangesum(above(sum(sale),0,12)),Month,grade))
1	A	10
1	B	30
1	C	60
2	A	10
2	B	30
2	C	60
3	A	10
3	B	30
3	C	60
4	A	10
4	B	30
4	C	60
5	A	10
5	B	30
5	C	60
6	A	10
6	B	30
6	C	60
7	A	10
7	B	30
7	C	60
8	A	10
8	B	30
8	C	60
9	A	10
9	B	30
9	C	60
10	A	10
10	B	30
10	C	60
11	A	10
11	B	30
11	C	60
12	A	10
12	B	30
12	C	60

I expect to see:

Month	grade	sum(aggr(rangesum(above(sum(sale),0,12)),Month,grade))
1	A	10
1	B	20
1	C	30
2	A	20
2	B	40
2	C	60
3	A	30
3	B	60
3	C	90

Any suggestions. I am not sure if I am doing this right. maybe above() is not the right function here.

I could do it longitudinal like this, which would give right results, but there are many such combination of dimensions in the actual data to do it this way.

Month	sum(aggr(rangesum(above( total sum({<Month=,grade={'A'}>}sale),0,12)),Month))	sum(aggr(rangesum(above( total sum({<Month=,grade={'B'}>}sale),0,12)),Month))	sum(aggr(rangesum(above( total sum({<Month=,grade={'C'}>}sale),0,12)),Month))
1	10	20	30
2	20	40	60
3	30	60	90
4	40	80	120
5	50	100	150
6	60	120	180
7	70	140	210
8	80	160	240
9	90	180	270
10	100	200	300
11	110	220	330
12	120	240	360

Re: Rolling 12 month sum using above() in multi dimensional pivot table

Thu, 16 Feb 2017 05:11:15 GMT

Try like this by removing grade from Aggr Calculation

sum(aggr(rangesum(above(sum(sale),0,12)),Month))

Re: Rolling 12 month sum using above() in multi dimensional pivot table

phoenix — Thu, 16 Feb 2017 05:21:02 GMT

if I remove grade in Aggr, I am getting all 'A's

Month	grade	sum(aggr(rangesum(above(sum(sale),0,12)),Month))
1	A	60
2	A	120
3	A	180
4	A	240
5	A	300
6	A	360
7	A	420
8	A	480
9	A	540
10	A	600
11	A	660
12	A	720

Re: Rolling 12 month sum using above() in multi dimensional pivot table

HirisH_V7 — Thu, 16 Feb 2017 05:21:52 GMT

Hi Siva,

Check this

By using,

Sum(sale)*Month

as Expression.

You can Drag the Grade Dimension and place it horizontally to get your required longitude approach.

-Hirish

Re: Rolling 12 month sum using above() in multi dimensional pivot table

prat1507 — Thu, 16 Feb 2017 05:38:07 GMT

This would only work if your Sales are same throughout the year for each month. What if the Sales are like

1 A 10

1 B 20

1 C 30

1 A 15

1 B 8

1 C 10

Regards
Pratyush

Re: Rolling 12 month sum using above() in multi dimensional pivot table

HirisH_V7 — Thu, 16 Feb 2017 05:46:14 GMT

I hope Pivot Will automatically Group those Month and Grade that gives you the result.

Re: Rolling 12 month sum using above() in multi dimensional pivot table

phoenix — Thu, 16 Feb 2017 05:51:59 GMT

Exactly, as mentioned by pratyush, this wouldn't work if the values are not in the same pattern.

Month	grade	range	sale
1	A	10 to 20	10
1	B	30 to 40	20
1	C	50 to 60	30
2	A	10 to 20	12
2	B	30 to 40	6
2	C	50 to 60	4
3	A	10 to 20	10
3	B	30 to 40	99
3	C	50 to 60	30
4	A	10 to 20	6
4	B	30 to 40	66
4	C	50 to 60	30
5	A	10 to 20	0
5	B	30 to 40	20
5	C	50 to 60	30
6	A	10 to 20	878
6	B	30 to 40	20
6	C	50 to 60	30
7	A	10 to 20	10
7	B	30 to 40	8
7	C	50 to 60	30
8	A	10 to 20	10
8	B	30 to 40	0
8	C	50 to 60	30
9	A	10 to 20	10
9	B	30 to 40	22
9	C	50 to 60	66
10	A	10 to 20	10
10	B	30 to 40	64
10	C	50 to 60	30
11	A	10 to 20	10
11	B	30 to 40	35
11	C	50 to 60	30
12	A	10 to 20	77
12	B	30 to 40	20
12	C	50 to 60	8
1	A	20 to 30	56
1	B	40 to 50	9
1	C	60 to 70	30
2	A	20 to 30	78
2	B	40 to 50	20
2	C	60 to 70	30
3	A	20 to 30	5
3	B	40 to 50	20
3	C	60 to 70	78
4	A	20 to 30	10
4	B	40 to 50	6
4	C	60 to 70	9
5	A	20 to 30	3
5	B	40 to 50	20
5	C	60 to 70	7
6	A	20 to 30	10
6	B	40 to 50	20
6	C	60 to 70	9
7	A	20 to 30	78
7	B	40 to 50	20
7	C	60 to 70	30
8	A	20 to 30	5
8	B	40 to 50	20
8	C	60 to 70	3
9	A	20 to 30	10
9	B	40 to 50	20
9	C	60 to 70	30
10	A	20 to 30	10
10	B	40 to 50	45
10	C	60 to 70	6
11	A	20 to 30	24
11	B	40 to 50	20
11	C	60 to 70	5
12	A	20 to 30	10
12	B	40 to 50	85
12	C	60 to 70	30

I am getting this:

Month	grade	*sum(sale)Month**
1	A	66
1	B	29
1	C	60
2	A	180
2	B	52
2	C	68

but I expect to see:

Month	grade	*sum(sale)Month**
1	A	66
1	B	29
1	C	60
2	A	156
2	B	55
2	C	94

Re: Rolling 12 month sum using above() in multi dimensional pivot table

prat1507 — Thu, 16 Feb 2017 06:27:05 GMT

Hi Siva

PFA the .qvw for your desired result.

Regards
Pratyush

Re: Rolling 12 month sum using above() in multi dimensional pivot table

prat1507 — Thu, 16 Feb 2017 09:50:09 GMT

Hi Siva

Did you check my solution, is it not working for you? Please let me know if that is the case.

Regards

Pratyush

Re: Rolling 12 month sum using above() in multi dimensional pivot table

phoenix — Thu, 16 Feb 2017 13:18:40 GMT

It's working, that is what I need. Thanks for your help Pratyush. So the logic here is to create a cross join? Could you help me understand the logic?

Re: Rolling 12 month sum using above() in multi dimensional pivot table

phoenix — Thu, 16 Feb 2017 15:05:07 GMT

I have these two tables:

LINE_ITEMS1:
Load * Inline [
Account, grade, range
1, A, 10-20
2, A, 20-30
3, A, 10-20
4, B, 30-40
5, B, 30-40
];

LINE_ITEMS2:
LOAD * Inline [
Month, Account, loss
1, 1, 0
2, 1, 0
3, 1, 0
4, 1, 100
5, 1, 0
1, 2, 0
2, 2, 0
3, 2, 0
4, 2, 0
5, 2, 0
6, 2, 0
7, 2, 0
8, 2, 0
9, 2, 0
10, 2, 22
11, 2, 0
12, 2, 0
1, 3, 0
2, 3, 0
3, 3, 0
4, 3, 0
5, 3, 0
6, 3, 0
7, 3, 0
8, 3, 0
9, 3, 0
10, 3, 50
11, 3, 0
12, 3, 0
1, 4, 0
2, 4, 0
3, 4, 0
4, 4, 0
5, 4, 0
6, 4, 0
7, 4, 0
8, 4, 0
9, 4, 0
10, 4, 11
11, 4, 0
12, 4, 0
1, 5, 0
2, 5, 0
3, 5, 0
4, 5, 0
5, 5, 0
6, 5, 0
7, 5, 0
8, 5, 0
9, 5, 0
10, 5, 200
11, 5, 0
12, 5, 0

];

I want to calculate rolling 12 month loss for each grade and range combination for each month.

desired result:

Month	grade	range	rolling_12_loss
1	A	10-20	0
1	A	20-30	0
1	B	30-40	0
2	A	10-20	0
2	A	20-30	0
2	B	30-40	0
3	A	10-20	0
3	A	20-30	0
3	B	30-40	0
4	A	10-20	100
4	A	20-30	0
4	B	30-40	0
5	A	10-20	0
5	A	20-30	0
5	B	30-40	0
6	A	10-20	0
6	A	20-30	0
6	B	30-40	0
7	A	10-20	0
7	A	20-30	0
7	B	30-40	0
8	A	10-20	0
8	A	20-30	0
8	B	30-40	0
9	A	10-20	0
9	A	20-30	0
9	B	30-40	0
10	A	10-20	150
10	A	20-30	22
10	B	30-40	211
11	A	10-20	150
11	A	20-30	22
11	B	30-40	211
12	A	10-20	150
12	A	20-30	22
12	B	30-40	211

any suggestions?

Thanks

Re: Rolling 12 month sum using above() in multi dimensional pivot table

prat1507 — Fri, 17 Feb 2017 04:47:20 GMT

I didn't get the logic, how come 12 A and 12 B are populated, while others are not. Please explain the logic.

Regards
Pratyush

Re: Rolling 12 month sum using above() in multi dimensional pivot table

phoenix — Fri, 17 Feb 2017 15:13:35 GMT

I am sorry , yes 5 - A - 10-20, 6 - A - 10-20 .... should also be populated.