Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try Below expression
if(RowNo()>1, RangeSum(Above(Sum(FieldC+FieldD),0,RowNo())), sum(FieldC+FieldD))
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;
Hi,
Please find attached file for solution.
Regards,
Jagan.
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
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