# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
New Contributor III

## Rolling Calculation with not so usual logic

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.

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
1 Solution

Accepted Solutions
Highlighted
New Contributor II

## Re: Rolling Calculation with not so usual logic

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:

[

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

*,

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;

4 Replies
Highlighted
Esteemed Contributor

## Re: Rolling Calculation with not so usual logic

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 ?

Highlighted
New Contributor III

## Re: Rolling Calculation with not so usual logic

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)

Highlighted
New Contributor II

## Re: Rolling Calculation with not so usual logic

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:

[

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

*,

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;

Highlighted
New Contributor III

Thanks Daniel,

it worked.

Best,

Bilal