topic Rolling Calculation with not so usual logic in QlikView

Rolling Calculation with not so usual logic

bilalgunay — Wed, 18 Jul 2018 07:55:45 GMT

Hi everyone,

I want to calculate the column in_hand_month_end with a specific logic.

For 201701, the value of the column is -18 and that comes from;

in_hand_month_end(201612) + produced_month_end(201701) - sold_month_end(201701)

0 + 344 - 362 = -18

How can this be done?

It can be done via script for now and I can use the end table with calculated column. (of course if possible with set analysis too in charts for the future)

One thing to consider: 201612 will be the first yearmonth, so for in_hand_month_end(201611) a value of 0 can be taken. Or simply, the calculation can start from 2nd row onwards.

Thanks in advance,

Bilal

yearmonth	plant	storage	material	produced_month_end	sold_month_end	in_hand_month_end
201612	9999	9999	11111	0	0	0
201701	9999	9999	11111	344	362	-18
201702	9999	9999	11111	407	396	-7
201703	9999	9999	11111	349	349	-7
201704	9999	9999	11111	419	433	-21
201705	9999	9999	11111	1190	1206	-37
201706	9999	9999	11111	1030	1009	-16
201707	9999	9999	11111	1098	1134	-52
201708	9999	9999	11111	474	428	-6
201709	9999	9999	11111	122	121	-5
201710	9999	9999	11111	219	217	-3
201711	9999	9999	11111	333	369	-39
201712	9999	9999	11111	389	379	-29
201804	9999	9999	11111	0	7	-36
201805	9999	9999	11111	0	11	-47
201806	9999	9999	11111	0	10	-57

Re: Rolling Calculation with not so usual logic

YoussefBelloum — Wed, 18 Jul 2018 08:28:48 GMT

Hi,

I'm missing something here..

you want to calculate the column in_hand_month_end, right ?

and here is how the calculation should be done: in_hand_month_end(201612) + produced_month_end(201701) - sold_month_end(201701).. right ?

how do you use that field in it's own calculation ?

Re: Rolling Calculation with not so usual logic

bilalgunay — Wed, 18 Jul 2018 08:48:54 GMT

It is a rolling calculation and it will not be used for the same month.

To be more precise:

in_hand_month_end(201701) = in_hand_month_end(201612) + produced_month_end(201701) - sold_month_end(201701)

Re: Rolling Calculation with not so usual logic

biirka — Wed, 18 Jul 2018 08:55:22 GMT

Hi,

I assumed that you had multiple plants and multiple materials and that you wanted to calculate a value per plant and material.

temp_Data:

LOAD * INLINE

[

yearmonth, plant, storage, material, produced_month_end, sold_month_end, in_hand_month_end

201612, 9999, 9999, 11111, 0, 0, 0

201701, 9999, 9999, 11111, 344, 362, -18

201702, 9999, 9999, 11111, 407, 396, -7

201703, 9999, 9999, 11111, 349, 349, -7

201704, 9999, 9999, 11111, 419, 433, -21

201705, 9999, 9999, 11111, 1190, 1206, -37

201706, 9999, 9999, 11111, 1030, 1009, -16

201707, 9999, 9999, 11111, 1098, 1134, -52

201708, 9999, 9999, 11111, 474, 428, -6

201709, 9999, 9999, 11111, 122, 121, -5

201710, 9999, 9999, 11111, 219, 217, -3

201711, 9999, 9999, 11111, 333, 369, -39

201712, 9999, 9999, 11111, 389, 379, -29

201804, 9999, 9999, 11111, 0, 7, -36

201805, 9999, 9999, 11111, 0, 11, -47

201806, 9999, 9999, 11111, 0, 10, -57

]

;

Data:

NOCONCATENATE

LOAD

IF (PREVIOUS(plant) = plant AND PREVIOUS(material) = material, PEEK(in_hand_month_end_calc), 0) + produced_month_end - sold_month_end AS in_hand_month_end_calc

RESIDENT temp_Data

ORDER BY

plant, material, yearmonth

;

DROP TABLE temp_Data;

Re: Rolling Calculation with not so usual logic

bilalgunay — Fri, 20 Jul 2018 13:57:16 GMT

Thanks Daniel,

it worked.

Best,

Bilal