topic Re: Accumulation in QlikView

Accumulation

n1ef5ng1 — Thu, 02 May 2013 03:55:26 GMT

Scenario, i have a five years set of data in a table that is display in monthly.

Jan - 2008

Feb - 2008

Dec - 2008

Jan - 2009

Mar - 2013

I need to create an accumulative column of values in year basic. meaning that once it hit the december of a particular year, it will reset back to 0 for jan - 2009

thanks!

Re: Accumulation

somenathroy — Thu, 02 May 2013 04:28:03 GMT

use below line with LOAD statement:

if( Left(YearMonth,4) = Left(Previous(YearMonth),4) , <Field_Name> + Peek('Accumulative'), <Field_Name>) as Accumulative

Regards,

Som

Re: Accumulation

n1ef5ng1 — Thu, 02 May 2013 05:01:14 GMT

possible to do it without editing the script?

Re: Accumulation

er_mohit — Thu, 02 May 2013 05:04:53 GMT

take year and value which based on year as dimension in pivot chart

and in expression

write this

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

hope it helps

Re: Accumulation

Anonymous — Thu, 02 May 2013 05:05:44 GMT

For taking only one year values you need to edit the script only..

use the following in ur script

Load *,

RowNo() as Row,

If(Year like 'Jan*',Value, Value+previous(Value)) as ValueNew

;

LOAD * INLINE [

Year, Value

Jan2010, 10

feb2010, 20

march2010, 30

april2010, 40

Jan2011, 50

feb2011, 60

march2011, 70

april2011, 80

];

Then using straight table or table box u can get the data from ValueNew

Hope it helps u

Regards

Revathy

Re: Accumulation

n1ef5ng1 — Thu, 02 May 2013 05:17:29 GMT

for

Year, Value

Jan2010, 10

feb2010, 20

march2010, 30

april2010, 40

Jan2011, 50

feb2011, 60

march2011, 70

april2011, 80

];

do i have to list out on this list or according to ALL my date on my table?

which is jan 2010 - march 2013

Re: Accumulation

Anonymous — Thu, 02 May 2013 05:19:51 GMT

According to ur dates u can take, If the field starts with 'jan*' then it wil take only the value else gets accumulated with previous value.

Regards

Revathy

Re: Accumulation

n1ef5ng1 — Thu, 02 May 2013 05:22:42 GMT

LOAD *,

RowNo() as Row,

if(Year like 'Jan*', Value, Value + Previous(Value)) as ValueNew

;

LOAD * Inline

[

Year,Value

Jan-2011,10

Feb-2011,20

Mar-2011,30

Apr-2011,40

May-2011,50

Jun-2011,60

Jul-2011,70

Aug-2011,80

Sep-2011,90

Oct-2011,100

Nov-2011,110

Dec-2011,120

];

this is just 2011, i have to add in 2012,2013?

next, do i have to add anything on the dimension or expression side?

Re: Accumulation

Anonymous — Thu, 02 May 2013 05:26:12 GMT

Yup, U can add all the data. If u have any problem please uplad the sample qvw.

Regards

Revathy.

Re: Accumulation

n1ef5ng1 — Thu, 02 May 2013 05:29:44 GMT

mine is a personal edition...

Upon adding the code on the script,

what is next? how am i going to retrieve the values of accumulation on Expressions

Re: Accumulation

Anonymous — Thu, 02 May 2013 05:41:07 GMT

Please find the attached qvw.

The 'ValueNew' Field can be obtained using this..

Expression level i am not sure...

Regards

Revathy