topic Re: Accumulation in QlikView

Accumulation

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am attaching my excel,

here ws1 is the field which i need to devide based on quantity.

i am getting like this

Part No	Posting Date	Quantity	Total Qty	ws1	Cumulative	outfield
000979940	4/1/2016	4	234	158	4	4
000979940	3/31/2016	100	234	158	104	100
000979940	3/16/2016	16	234	158	120	16
000979940	3/2/2016	100	234	158	220	38
000979940	2/18/2016	4	234	158	224	38
000979940	2/16/2016	17	234	158	241	38
000979940	5/23/2015	-4	234	158	237	38
000979940	5/22/2015	-20	234	158	217	38
000979940	5/21/2015	-2	234	158	215	38
000979940	5/20/2015	-7	234	158	208	38
000979940	5/19/2015	-4	234	158	204	38
000979940	5/18/2015	-6	234	158	198	38
000979940	5/16/2015	-16	234	158	182	38
000979940	5/15/2015	-4	234	158	178	38
000979940	5/14/2015	-8	234	158	170	38
000979940	5/12/2015	-12	234	158	158	-12
000979940	11/20/2014	2	234	158	160	38
000979940	10/23/2014	70	234	158	230	38
000979940	10/21/2014	13	234	158	243	38
000979940	5/21/2014	-8	234	158	235	38
000979940	11/12/2013	-1	234	158	234	0

i want output like below.

my alocated it has to show 158 because ws1 is 158.

outfield should get from quantity.

Part No	Posting Date	Quantity	Total Qty	ws1	Cumulative	outfield
000979940	4/1/2016	4	234	158	4	4
000979940	3/31/2016	100	234	158	104	100
000979940	3/16/2016	16	234	158	120	16
000979940	3/2/2016	100	234	158	220	38
000979940	2/18/2016	4	234	158	224	0
000979940	2/16/2016	17	234	158	241	0
000979940	5/23/2015	-4	234	158	237	0
000979940	5/22/2015	-20	234	158	217	0
000979940	5/21/2015	-2	234	158	215	0
000979940	5/20/2015	-7	234	158	208	0
000979940	5/19/2015	-4	234	158	204	0
000979940	5/18/2015	-6	234	158	198	0
000979940	5/16/2015	-16	234	158	182	0
000979940	5/15/2015	-4	234	158	178	0
000979940	5/14/2015	-8	234	158	170	0
000979940	5/12/2015	-12	234	158	158	0
000979940	11/20/2014	2	234	158	160	0
000979940	10/23/2014	70	234	158	230	0
000979940	10/21/2014	13	234	158	243	0
000979940	5/21/2014	-8	234	158	235	0
000979940	11/12/2013	-1	234	158	234	0

Re: Accumulation

Thu, 07 Apr 2016 06:12:39 GMT

Hi Any idea.

Re: Accumulation

ramasaisaksoft — Thu, 07 Apr 2016 06:13:13 GMT

Hi Janith,

For Straight table properties->Expressions->call "Quantity" as a expression and there you can find options Left hand side of your Expression name

3 options

No Accumulation

Full Accumulation

Partial or some values Accumulation

then u need to select Full accumulation.

Re: Accumulation

perumal_41 — Thu, 07 Apr 2016 06:13:44 GMT

use full accumulation like below

Re: Accumulation

Thu, 07 Apr 2016 06:15:30 GMT

Hi perumal,

thanks for replay,

this need to done in script

Re: Accumulation

Thu, 07 Apr 2016 06:16:27 GMT

HI Rama,

Thanks for replay,

This need to be done in script

Re: Accumulation

Kushal_Chawda — Thu, 07 Apr 2016 06:18:17 GMT

What would be the output if you have multiple ws1?

Re: Accumulation

Thu, 07 Apr 2016 06:25:16 GMT

ws1 is same for particular id

example:

000979940-158

000979941-170

000979942-190

Re: Accumulation

jagan — Thu, 07 Apr 2016 06:33:17 GMT

Hi,

Try like this using Previous() like below

Data:

LOAD

RangeSum(Previous(AccumSum), Quantity) AS AccumSum

FROM DataSource;

Data:

LOAD

RangeSum(Peek(AccumSum), Quantity) AS AccumSum

FROM DataSource;

Regards,

Jagan.

Re: Accumulation

Kushal_Chawda — Thu, 07 Apr 2016 06:33:34 GMT

then try this

Data:

LOAD

[Part No]

[Posting Date]

Quantity

Total Qty

ws1

FROM Table;

Accumulate:

noconcatenate

LOAD *,

if(rowno()=1 or ws1<>previous(ws1),Quantity, rangesum(peek('Cumulative'),Quantity)) as Cumulative

Resident Data

order by ws1,[Posting Date] desc;

drop table Data;

Re: Accumulation

perumal_41 — Thu, 07 Apr 2016 06:34:31 GMT

please look the application . this meet your requirement .

Re: Accumulation

Thu, 07 Apr 2016 06:43:14 GMT

This is giving same as accumulation but my output id different please outfield in previous post

Re: Accumulation

Thu, 07 Apr 2016 06:43:40 GMT

This is giving same as accumulation but my output id different please outfield in previous post

Re: Accumulation

Thu, 07 Apr 2016 06:50:16 GMT

Hi Jagan

Thanks for replay but

This is not working for my requirement cumulation filed already i have i need outfiled as output in my post

Re: Accumulation

Kushal_Chawda — Thu, 07 Apr 2016 06:56:54 GMT

How outfield is calculated? what is logic?

Re: Accumulation

Thu, 07 Apr 2016 07:04:42 GMT

load *,

if(Cumulative<=ws1,Quantity) as outfield1,

resident abc;

drop table abc;

left join*(abc)

load partno,

sum(if(outfield1>0,outfield1) as outfield2

resident abc group by partno;

final:

load *,

if(outfield1>0,outfield1,if((ws1-outfield2)<0,0,ws1-outfield2)) as outfield

resident abc;

drop table abc;

this is the logic for outfield but i am getting outfield as my original post

Re: Accumulation

Kushal_Chawda — Thu, 07 Apr 2016 07:24:50 GMT

try this

Data:

LOAD

[Part No]

[Posting Date]

Quantity

Total Qty

ws1

Cumulative

FROM Table;

Accumulate:

noconcatenate

LOAD *,

if(Cumulative<= ws1,Quantity, if(Cumulative>ws1, ws1-rangesum(peek('Outfield'),Quantity))) as Outfield

Resident Data

order by ws1,[Posting Date] desc;

drop table Data;

Re: Accumulation

Thu, 07 Apr 2016 07:30:54 GMT

I tried i am getting like

Outfield
4
100
16
42
112
29
133
45
115
50
112
52
122
40
126
-12
168
-80
225
-59
218

but i need

Outfield
4
100
16
38
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Re: Accumulation

Kushal_Chawda — Thu, 07 Apr 2016 08:39:35 GMT

Please check if statements parenthesis

Data:

LOAD

[Part No]

[Posting Date]

Quantity

Total Qty

ws1

Cumulative

FROM Table;

Accumulate:

noconcatenate

LOAD *,

if(Cumulative<= ws1,Quantity,

if(ws1=previous(ws1) and Cumulative<= ws1,rangesum(peek('Outfield'),Quantity),

if(ws1=previous(ws1) and Cumulative>ws1, ws1-rangesum(peek('Outfield'),Quantity))))) as Outfield

Resident Data

order by ws1,[Posting Date] desc;

drop table Data;

Re: Accumulation

Thu, 07 Apr 2016 09:07:52 GMT

Hi Kushal,

Still not getting