Running Total Limited By SubTotals

[ngrunoz]

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.

[Anil_Babu_Samineni]

What is the expected result from given data?

[ngrunoz]

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.  

[Anil_Babu_Samineni]

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

[effinty2112 · Accepted solution]

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

[ngrunoz]

Thanks So much.

[effinty2112]

You're very welcome Rungano!