topic Re: Running Total Limited By SubTotals in QlikView

Running Total Limited By SubTotals

ngrunoz — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I have a scenario where I have to do a delivery Schedule from one Customer to the next. I therefore need to have a running Total from 1st Customer to the next to a total QTY of let say 25 for each Suburb as this will be the total QTY which each truck can carry at a given time. How best can I approach this.

Re: Running Total Limited By SubTotals

Anil_Babu_Samineni — Wed, 30 Aug 2017 11:35:36 GMT

What is the expected result from given data?

Re: Running Total Limited By SubTotals

ngrunoz — Wed, 30 Aug 2017 11:44:00 GMT

The expectation is to find Which Customers will be serviced by Which Truck and which Truck has been used to full capacity and below Capacity based on the 25 tons (QTY) capacity.

Customers Grouped by Suburb and Town to a maximum of 25 tons.

Hope I have explained it in a way that's understandable.

Re: Running Total Limited By SubTotals

Anil_Babu_Samineni — Wed, 30 Aug 2017 12:08:14 GMT

may be explanation clear, Better if you provide output from data set which provided by you

Re: Running Total Limited By SubTotals

effinty2112 — Wed, 30 Aug 2017 12:31:40 GMT

Hi Rungano,

Try:

Data:

LOAD CITY,

TOWN,

SURBURB,

CUSTOMER,

QTY

FROM

[Delivery Schedule.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Data)

Load

SURBURB,

CUSTOMER,

if(Peek(SURBURB) =SURBURB, QTY + Peek(RunningQty),QTY) as RunningQty,

SURBURB & ' - ' & Div(24+if(Peek(SURBURB) =SURBURB, QTY + Peek(RunningQty),QTY),25) as Truck

Resident Data

Order by CITY, TOWN, SURBURB;

To get this:

CITY	TOWN	SURBURB	CUSTOMER	QTY	RunningQty	Truck
C1	T1	S1	C1	5	5	S1 - 1
C1	T1	S1	C2	10	15	S1 - 1
C1	T1	S1	C3	12	27	S1 - 2
C1	T1	S1	C4	6	33	S1 - 2
C1	T1	S1	C5	3	36	S1 - 2
C1	T1	S2	S2C1	15	15	S2 - 1
C1	T1	S2	S2C2	12	27	S2 - 2
C1	T1	S2	S2C3	22	49	S2 - 2
C1	T1	S2	S2C4	10	59	S2 - 3
C1	T1	S3	S3C5	13	13	S3 - 1
C1	T1	S4	S4C1	11	11	S4 - 1
C1	T1	S4	S4C2	9	20	S4 - 1
C1	T1	S4	S4C3	18	38	S4 - 2
C1	T1	S4	S4C4	23	61	S4 - 3
C1	T2	T2SS1	T2SSC1	4	4	T2SS1 - 1
C1	T2	T2SS1	T2SSC2	6	10	T2SS1 - 1
C1	T2	T2SS1	T2SSC3	9	19	T2SS1 - 1

Cheers

Andrew

Re: Running Total Limited By SubTotals

ngrunoz — Wed, 30 Aug 2017 13:03:24 GMT

Thanks So much.

Re: Running Total Limited By SubTotals

effinty2112 — Wed, 30 Aug 2017 13:04:03 GMT

You're very welcome Rungano!