topic Re: Accumulative in QlikView

Accumulative

n1ef5ng1 — Tue, 07 May 2013 06:58:48 GMT

Hi Guys,

Is there any way I can create such a accumulation on my expression?

Desire output

Date	Value	Accumulative Value
Jan 2011	1	1
Feb 2011	2	3
Mar 2011	7	10
Apr 2011	4	14
May 2011	5	19
June 2011	8	27
July 2011	10	37
Aug 211	2	39
Sep 2011	7	46
Oct 2011	2	48
Nov 2011	1	49
Dec 2011	8	57
Jan 2012`	9	9
Feb 2012	1	10
March 2012	9	19

The accumulative value will reset once it reach end of Dec. Is there any way I am able to do that?

I am using this code at the moment rangesum(above(sum(TEU),0,mod(month(Year),12)+1)), and it is not working. thanks

Re: Accumulative

kumarnatarajan — Tue, 07 May 2013 07:08:03 GMT

Hi,

Try this one.

RangeSum

(Above(Sum(Value),0,RowNo()))

see the attached file

Re: Accumulative

kumarnatarajan — Tue, 07 May 2013 07:18:12 GMT

Hi,

Else one another way for Accumulative. see below screen short

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 07:20:01 GMT

I am using personal edition, could not open your qvw file.

regarding your code, I am unable to get the desired value.

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 07:42:56 GMT

Full accumulation will continue to accumulate even if it move to next year. I want it to reset back to 0 when is a new year.

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 08:07:49 GMT

hello any help~~ kinda urgent

Re: Accumulative

Sokkorn — Tue, 07 May 2013 08:23:35 GMT

Hi,

You can try this load script

[Data]:

LOAD * Inline [

Period , Value

Jan 2011 , 1

Feb 2011 , 2

Mar 2011 , 7

Apr 2011 , 4

May 2011 , 5

Jun 2011 , 8

Jul 2011 , 10

Aug 2011 , 2

Sep 2011 , 7

Oct 2011 , 2

Nov 2011 , 1

Dec 2011 , 8

Jan 2012 , 9

Feb 2012 , 1

Mar 2012 , 9];

[Acc]:

LOAD

Period,

Value,

RangeSum([Value], Peek('AccVal1')) AS [AccVal1],

IF(Right([Period],4) = Previous(Right([Period],4)), RangeSum([Value], Peek('AccVal2')),[Value]) AS [AccVal2]

Resident [Data];

DROP table [Data];

Regards,

Sokkorn

Re: Accumulative

er_mohit — Tue, 07 May 2013 09:54:48 GMT

try this

rangesum(above(sum(Value),0,RowNo()))

see the attached file it works for me

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 10:03:02 GMT

I tried your formula.

My data range from Jan 2011 - Apr 2013

Jan to Dec 2011 the value is 20000000

Jan 2012 it still accumulate from 20000000 to 23300000

However my desire output is Jan 2012 to reset back to 0 and accumulate from there

Re: Accumulative

er_mohit — Tue, 07 May 2013 10:05:15 GMT

yeah same in above attached application and see the expression acc

it accumulate like what you want

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 10:16:35 GMT

The value that you mentioned, is it referring to the value that i name on the table?

Re: Accumulative

Sokkorn — Tue, 07 May 2013 10:18:37 GMT

Hi,

Did you try my script above?

Rgds,

Sokkorn

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 10:20:25 GMT

Hi i am unable to edit the script as it might affect the rest of the sheets. terribly sorry about that

Re: Accumulative

er_mohit — Tue, 07 May 2013 10:24:58 GMT

in my application i used dimension year and ncount

and expression accumulate

see in my application it works for per year accumulation

Re: Accumulative

n1ef5ng1 — Tue, 07 May 2013 10:29:02 GMT

sorry but what is ncount for?

Re: Accumulative

MK_QSL — Tue, 07 May 2013 10:33:09 GMT

Use Year(Date) as Year while loading script...

While making table, use Year as dimension and hide it using presentation tab is not needed.

In Expression tab, use Full Accululation as shown in earlier reply by some of our friends.

Hope this will help.

Re: Accumulative

er_mohit — Tue, 07 May 2013 10:48:48 GMT

ncount and year is my fieldname

i post you our example as per your need