topic Incremental sum in QlikView

Incremental sum

Thu, 22 Jul 2010 16:37:25 GMT

Hello!

I'm looking for a formula that can do this kind of sum. I've called it "incremental sum" but I don't know the proper name.

Value Sum
------------
10 10
8 18 (=10+8)
2 20 (=18+2)
5 25 (=20+5)

Since I don't know how it is called I can't look for it easily on the web. I've tried also with above() but it doesn't work (I'm definitely doing something wrong).

Thank you!

Incremental sum

Thu, 22 Jul 2010 16:39:27 GMT

I think it's worth telling you why I need this, because, who knows, maybe there's a better solution!
Having a table with customers and relative sales, descending order, I need to give a specific code for those who make the 80% of the total sales, another for those who make the 15% and another to the others.

Incremental sum

Fri, 23 Jul 2010 13:07:13 GMT

Hi mate,

you can use full accumulation in expression tab.

try this !

Naren.

Incremental sum

Fri, 23 Jul 2010 14:20:04 GMT

Thank you NarenB4U for your answer: you also gave me the English name of the kind of sum I'm looking for!

Even if I tried your solution, though, it works only with one dimension. Anyhow, I looked around (thanks again to the English proper name) and I found this working formula: rangesum(above(total thisColumn), sum(Sales)).

Everything works, but I can't order the table for the Sales value when I have this formula activated. Any idea how can I fix this, please?

Incremental sum

Fri, 23 Jul 2010 14:51:44 GMT

temp_Data:
LOAD * INLINE [
Value
10
18
23
45
56
];

load Value,
if( ISNUll(peek(Value)),Value,Value+peek(SUM)) as SUM

Resident temp_Data;

Drop temp_Data;

I tried the above code and it is working fine. I ain't sure, this is what you are looking for..

Incremental sum

Fri, 23 Jul 2010 15:13:51 GMT

Thank you, Naive, but I can't adopt your solution since I need this table to be calculated on the fly according to the filters I set. The script reported is perfect, but it has this bad drawback...

Thank you anyway for your support!

Incremental sum

suniljain — Fri, 23 Jul 2010 15:46:01 GMT

X1:
Load distinct
Trim(Material_Code) as Material,
Trim(Plant_Code) as Plant,
Trim(Storage_Location) as Storage_Location,
S_Date ,
Receipt_Count,
Issue_Count,
Value_Issued_From_Valuated_Stock as Issued_Value,
Value_Received_Into_Valuated_Stock as Received_Value

FROM
$(ModelledQVD)Stock_Master.qvd(qvd) ;

X:
Load distinct
Material,
Plant,
S_Date ,
SUM(Received_Value - Issued_Value) as Value

resident X1 group by Plant, Material,S_Date ;
Drop table X1;

Y:
load *,recno() as d resident X order by Plant,Material,S_Date;
drop table X;

//exit script;
Daily_Stock_Value:
Load
Material,
Plant,
S_Date,
Value,
if (peek(Material)=Material and peek(Plant)=Plant ,peek(Value_Output)+Value,Value) as Value_Output
resident Y order by d;
drop field d;
drop table Y;
Store Daily_Stock_Value into $(ModelledQVD)Daily_Stock_Value.qvd;
drop table Daily_Stock_Value;