topic Re: Hello Everyone, Working in a straight table chart need help. in QlikView

Hello Everyone, Working in a straight table chart need help.

Wed, 13 Nov 2013 07:34:04 GMT

Hello,

I am working in a straight table where i have table like as:

Load * Inline [

FieldA,FieldB,FieldC,FieldD

A, 01-01-2013, 200,100

B, 01-01-2013, 100, 300

C, 01-01-2013, 50, 400

A, 02-02-2013, 0, 500

B, 02-02-2013, 0, 600

C, 02-02-2013, 0, 1000

A, 10-03-2013, 0, 500

B, 10-03-2013, 0, 600

C, 10-03-2013, 0, 1000

];

and i want to do like as:

FieldA FieldB FieldC FieldD FieldE

A 01-01-2013 200 100 (200+100)

A 02-02-2013 0 500 ((500+100)+200)

A 10-03-2013 0 500 (500+500+100)+200

that means i am tracing the For each FieldA and FieldB , sum all the above values with the current 1 of FieldD and then adding FieldC=200 which will give us FieldE.

Thanks and Regards.

Re: Hello Everyone, Working in a straight table chart need help.

Wed, 13 Nov 2013 08:52:21 GMT

Try Below expression

if(RowNo()>1, RangeSum(Above(Sum(FieldC+FieldD),0,RowNo())), sum(FieldC+FieldD))

Re: Hello Everyone, Working in a straight table chart need help.

bbi_mba_76 — Wed, 13 Nov 2013 09:07:49 GMT

Hi,

maybe like this, if you want to do in the script

tb:

Load *,

FieldC+FieldD as FieldE

;

Load * Inline [

FieldA,FieldB,FieldC,FieldD

A, 01-01-2013, 200,100

B, 01-01-2013, 100, 300

C, 01-01-2013, 50, 400

A, 02-02-2013, 0, 500

B, 02-02-2013, 0, 600

C, 02-02-2013, 0, 1000

A, 10-03-2013, 0, 500

B, 10-03-2013, 0, 600

C, 10-03-2013, 0, 1000

];

Join

Load FieldA, FieldB,

rangesum(FieldE, if(FieldA=previous(FieldA), peek('FieldE2'), 0) ) AS FieldE2

resident tb

order by FieldA, FieldB

;

Drop field FieldE;

rename field FieldE2 to FieldE;

Re: Hello Everyone, Working in a straight table chart need help.

jagan — Wed, 13 Nov 2013 09:11:59 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Hello Everyone, Working in a straight table chart need help.

Wed, 13 Nov 2013 09:28:03 GMT

Hi,

in straight table:

Dimensions set to FieldA,FieldB

Expressions to FieldC,FieldD and one calculated aggr(rangesum( above( sum(FieldD+FieldC),0,12) ),FieldA,FieldB)

and check the accumulation checkbox

this will make the running sum for a sum of C+D

this link can help you

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums

Re: Re: Hello Everyone, Working in a straight table chart need help.

Mon, 18 Nov 2013 15:49:44 GMT

Hi,

There was a small error in the test.qvw from Jagan, because FieldC was not included.

I tweaked it a bit and now it is correct.

But it stll remains the solution of Jagar

Hope it helps,

Regards,

Gerrit