topic Re: Accumulation consumptions group by Material_no, Year, Month in script in QlikView

Accumulation consumptions group by Material_no, Year, Month in script

thanhphongle — Mon, 03 May 2021 07:41:11 GMT

Hello together

how can I accumulate my consumption by Material_No, Calendar Year and Calendar Month in the script??

The result should look like in the Column "Expected_Accumulation_Consumption"

Material_No	Calendar Year	Calendar Month	Calendar Date	Consumption Quantity
1001	2020	July	31.07.2020	0
1001	2020	August	31.08.2020	-2
1001	2020	September	30.09.2020	0
1001	2020	October	31.10.2020	-2
1001	2020	November	30.11.2020	0
1001	2020	December	31.12.2020	0
1001	2021	January	31.01.2021	-2
1001	2021	February	28.02.2021	-4
1001	2021	March	31.03.2021	-10
1002	2020	July	31.07.2020	0
1002	2020	August	31.08.2020	-6
1002	2020	September	30.09.2020	-2
1002	2020	October	31.10.2020	-14
1002	2020	November	30.11.2020	-5
1002	2020	December	31.12.2020	-1
1002	2021	January	31.01.2021	-1
1002	2021	February	28.02.2021	-4
1002	2021	March	31.03.2021	-9

The goal is to have an additional Field which should look like this

Expected_Accumulation_Consumption
0
-2
-2
-4
-4
-4
-2
-6
-16
0
-6
-8
-22
-27
-28
-1
-5
-14

Hope someone can help me out here.

Thank you

Re: Accumulation consumptions group by Material_no, Year, Month in script

edwin — Sun, 02 May 2021 22:40:53 GMT

theres a lot of discussions on accumulation, Kush has a great example,

https://community.qlik.com/t5/QlikView-App-Dev/Hot-to-get-the-full-accumulation-when-selection-a-field/td-p/1044807

if this does not apply to you just look it

Re: Accumulation consumptions group by Material_no, Year, Month in script

MayilVahanan — Mon, 03 May 2021 02:04:29 GMT

Hi @thanhphongle

In script, try like below

LOAD *, If(Material_No = Peek(Material_No) and CalendarYear = Peek(CalendarYear),ConsumptionQuantity+Peek(AccConsumptionQuantity), ConsumptionQuantity) as AccConsumptionQuantity INLINE [
Material_No, CalendarYear, Calendar Month, Calendar Date, ConsumptionQuantity
1001, 2020, July, 31.07.2020, 0
1001, 2020, August, 31.08.2020, -2
1001, 2020, September, 30.09.2020, 0
1001, 2020, October, 31.10.2020, -2
1001, 2020, November, 30.11.2020, 0
1001, 2020, December, 31.12.2020, 0
1001, 2021, January, 31.01.2021, -2
1001, 2021, February, 28.02.2021, -4
1001, 2021, March, 31.03.2021, -10
1002, 2020, July, 31.07.2020, 0
1002, 2020, August, 31.08.2020, -6
1002, 2020, September, 30.09.2020, -2
1002, 2020, October, 31.10.2020, -14
1002, 2020, November, 30.11.2020, -5
1002, 2020, December, 31.12.2020, -1
1002, 2021, January, 31.01.2021, -1
1002, 2021, February, 28.02.2021, -4
1002, 2021, March, 31.03.2021, -9
];

Hope ur Materialno & year are in ascending order, if not , u need to use order by.

Re: Accumulation consumptions group by Material_no, Year, Month in script

thanhphongle — Mon, 03 May 2021 07:39:48 GMT

Hello @MayilVahanan ,

thank you for your help.

In your solution I dont have the field Peek(AccConsumptionQuantity).

In the table I provided the field "Expected_Accumulation_Consumption" was just an example how the values should look like.

Do you have another solution ?

Thank you in advance

Re: Accumulation consumptions group by Material_no, Year, Month in script

MayilVahanan — Mon, 03 May 2021 07:48:01 GMT

Hi @thanhphongle

AccConsumptionQuantity is the new field , not from ur data..

Below are ur fields..

Material_No, CalendarYear, Calendar Month, Calendar Date, ConsumptionQuantity.

Please look the logic.

Re: Accumulation consumptions group by Material_no, Year, Month in script

thanhphongle — Mon, 03 May 2021 08:53:02 GMT

It works, perfect. Thank you so much!!!

Re: Accumulation consumptions group by Material_no, Year, Month in script

MayilVahanan — Mon, 03 May 2021 09:37:03 GMT

You're welcome