Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Specialist

## Running Totals for Multiple Part Numbers

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

1 Solution

Accepted Solutions

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;

talk is cheap, supply exceeds demand
2 Replies

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;

talk is cheap, supply exceeds demand
Specialist
Author

☺ I knew that.

Thanks Gysbert!

Community Browser