Qlik Community

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
bilalgunay
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.

Thanks in advance,

Bilal

     

yearmonthplantstoragematerialproduced_month_endsold_month_endin_hand_month_end
2016129999999911111000
2017019999999911111344362-18
2017029999999911111407396-7
2017039999999911111349349-7
2017049999999911111419433-21
201705999999991111111901206-37
201706999999991111110301009-16
201707999999991111110981134-52
2017089999999911111474428-6
2017099999999911111122121-5
2017109999999911111219217-3
2017119999999911111333369-39
2017129999999911111389379-29
201804999999991111107-36
2018059999999911111011-47
2018069999999911111010

-57

1 Solution

Accepted Solutions
Highlighted
biirka
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:

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;

View solution in original post

4 Replies
Highlighted
YoussefBelloum
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
bilalgunay
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
biirka
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:

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;

View solution in original post

Highlighted
bilalgunay
New Contributor III

Re: Rolling Calculation with not so usual logic

Thanks Daniel,

it worked.

Best,

Bilal