topic Re: Accumulation Not working for Calculating other columns in QlikView

Accumulation Not working for Calculating other columns

maahivee — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys - I have a simple requirement here, But for some reason it is not working..

In a straight table, i have columns added picks, completed picks, which are regular calculations based on data from tables.

After that i have Begin, added picks cumulative and completed picks cumulative which are same calculations as above but i am giving "full accumulation" for these 3 columns.

And then i have # of Picks which is basically "begin + added picks cumulative - completed picks cumulative".

So when i am doing this # of picks column calculation based on the Full Accumulation columns, I would assume it showing results based on Full accumulation value for that row instead showing result based on the actual value..

Is there any thing i am missing here. or doing anything wrong. Please see the attached sheet for more data.

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 14:44:10 GMT

When you use the regular accumulation feature, that is the regular behaviour.

You need to use the Rangesum(Above(yourexpression),0.rowno())) in order to obtain your accumulation value

Please read this explanation: Range Sum

Re: Accumulation Not working for Calculating other columns

sunny_talwar — Mon, 13 Jun 2016 14:45:02 GMT

I think you would probably need to use RangeSum(Above(...)) instead of Full Accumulation to make this to work. I can't comment more because I don't have the expression or a sample, but this should guide you in the right direction. If you still have trouble, I would suggest providing a sample with your expected output

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 14:47:21 GMT

Rangesum(Above("Begin"-"Completed Picks Cummulative"+"Added Picks Cummulative"),rowno(),0)

I have tried this but giving 1 as result for all rows.

Re: Accumulation Not working for Calculating other columns

sunny_talwar — Mon, 13 Jun 2016 14:49:21 GMT

Again I am not 100% sure, but may be this:

RangeSum(Above("Begin"-"Completed Picks Cummulative"+"Added Picks Cummulative", 0, RowNo()))

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 14:50:08 GMT

Hello Sunny - PFA.. Column D (Desired Result) Column is the answer i am looking for.

Hour	Begin	# of Picks	Desired Result	Added Picks	Added Picks Cummulative	Completed Picks	Completed Picks Cummulative
	101	331		203	203	128	128
5	101	0	101	0	0	0	0
7	101	19	120	19	19	0	0
8	101	190	110	90	109	100	100
9	101	122	176	94	203	28	128

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 14:50:40 GMT

What are the expressions for "Begin" and other fields?

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 14:52:40 GMT

This is again giving the same result as not using the accumulation column.

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 14:53:47 GMT

=Count({<StatusType={'Begin'}>}TransactionAmt) for Begin

=Count({<StatusType={'Added'}>}TransactionAmt) for added

=Count({<StatusType={'Completed'}>}TransactionAmt) for completed

Same expression but giving "full accumulation" for cumulative columns.

Re: Accumulation Not working for Calculating other columns

sunny_talwar — Mon, 13 Jun 2016 14:55:32 GMT

What is the expression for # of Picks?

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 15:02:17 GMT

# of Picks = "begin + added picks cumulative - completed picks cumulative".

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 15:03:39 GMT

This should work

Rangesum(

Above(Count({<StatusType={'Begin'}>}TransactionAmt),0, rowno()),

-Above(Count({<StatusType={'Added'}>}TransactionAmt),0, rowno()),

Above(Count({<StatusType={'Completed'}>}TransactionAmt),,0 rowno())

)

If doesn´t please share a sample of your qvw Preparing examples for Upload - Reduction and Data Scrambling

Re: Accumulation Not working for Calculating other columns

sunny_talwar — Mon, 13 Jun 2016 15:09:58 GMT

This you mean?

Rangesum(

Above(Count({<StatusType={'Begin'}>}TransactionAmt), 0, RowNo()),

-Above(Count({<StatusType={'Added'}>}TransactionAmt), 0, RowNo()),

Above(Count({<StatusType={'Completed'}>}TransactionAmt), 0, RowNo()))

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 15:16:51 GMT

Please find the attached QVW with my actual data.

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 15:17:30 GMT

Yes, fixed as you suggested, stalwar1‌

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 15:22:46 GMT

Do you really need that 2nd dimension?

Re: Accumulation Not working for Calculating other columns

Clever_Anjos — Mon, 13 Jun 2016 15:24:09 GMT

PFA

Re: Accumulation Not working for Calculating other columns

sunny_talwar — Mon, 13 Jun 2016 15:34:38 GMT

Can you try this:

Rangesum(

Above(TOTAL Count({<StatusType={'Begin'}>}TransactionAmt), 0, RowNo(TOTAL)),

Above(TOTAL Count({<StatusType={'Added'}>}TransactionAmt), 0, RowNo(TOTAL)),

-Above(TOTAL Count({<StatusType={'Completed'}>}TransactionAmt), 0, RowNo(TOTAL)))

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 15:39:41 GMT

Hello Anjos - I dont know why that same expression in that QVW you attached is not working in my actual QVW.

Giving the same numbers as before.

Re: Accumulation Not working for Calculating other columns

maahivee — Mon, 13 Jun 2016 15:49:25 GMT

Hello Sunny - If we look at hour 9 it should be 101+262-131 = 232 it is instead 332

hour 10 also 101+323-134 = 290 it is instead 421.