topic Running Totals for Multiple Part Numbers in QlikView

Running Totals for Multiple Part Numbers

johnca — Thu, 11 Jul 2013 16:05:46 GMT

I have a script that pulls from a QVD and am counting a running total. Here's an excerpt;

Ship:

Load

Part_Number,

Serial_Number,

Ship_Date,

Ship_Quantity,

If(IsNull(Previous(Ship_Quantity)),Ship_Quantity,Peek('Ship_Running_Total') + Ship_Quantity) as Ship_Running_Total

From Ship.qvd;

What it does it does well, but it lumps all the part numbers together so that if I select a specific part number I get the running total of all part numbers, not just the one I am interested in.

How can I get the running total for each part number?

Is a For-Next loop the way to do it? If so, how would that work?

Many regards,

John

Re: Running Totals for Multiple Part Numbers

Gysbert_Wassenaar — Thu, 11 Jul 2013 16:16:43 GMT

You can use a resident load to order the table by Part_Number and Ship_Date so you can calculate the running totals per Part_Number:

Ship:

Load

Part_Number,

Serial_Number,

Ship_Date,

Ship_Quantity,

From Ship.qvd;

Result:

load

Part_Number

,Serial_Number

,Ship_Date

,Ship_Quantity

,if(previous(Part_Number)=Part_Number,

rangesum(peek('Ship_Running_Total'),Ship_Quantity),

Ship_Quantity

) as Ship_Running_Total

resident Ship

order by Part_Number,Ship_Date;

drop table Ship;

Re: Running Totals for Multiple Part Numbers

johnca — Thu, 11 Jul 2013 16:37:11 GMT

☺ I knew that.

Thanks Gysbert!