Getting Line wise different with Total and remaining Difference

[brijeshvma]

Hi All,

I need urgent help from scripting side

CustomerId	Delivery Date	Order Qty	Total Order Qty	Total Delivered Qty	Delivered Line Wise Item Qty	Pending Qty
1001	20-09-14	4	904	753	4	0
1001	15-10-15	200	904	753	200	0
1001	15-11-15	200	904	753	200	0
1001	15-12-15	200	904	753	200	0
1001	15-01-16	200	904	753	149	51
1001	16-01-16	100	904	753	0	100

I have data something like above first five table and and want output for last 2 column..

its like i have Totao order quantity and 4th column and line wise order qty .. and as well i have against the order how much total i have

delivered ( 904 - 753 = Remaining Pending qty 151) ... but i want to show data like last 2 column .  it means date wise first first order of 4 qty fulfil then second order qty and so on .. but once its reached the total of total delivered qty its show in Pending qty column

Kindly help me to resolve this..

As well I am attaching a sample excel file with required data in Yellow column.

[brijeshvma]

Can any one give me idea to resolve it

[Anonymous]

could you  change the yellow columns into expression in you excel file please...

[brijeshvma]

Hi Robin,

Which expression ?

As i created dummy data .. what client looking for .. I currently need logic to make the data as yellow column.

[eliran]

Hi Brijesh,

You're asking the community to help you find out the logic behind your customer's excel spread sheet.

I suggest you ask *him* that question, we can help you with calculations and methods to work it out afterwards.

BR,

Eliran.

[brijeshvma]

Hi Eliran ,

What i understand with Requirement ... let me try to explain.. Say for Customer 1001

	Delivery Date	Order Qty	Total Order Qty	Total Delivered Qty	Delivered Line Wise Item Qty	Pending Qty
	20-09-14	4	904	753	4	0
	15-10-15	200	904	753	200	0
	15-11-15	200	904	753	200	0
	15-12-15	200	904	753	200	0
	15-01-16	200	904	753	149	51
	16-01-16	100	904	753	0	100

We have a Line wise order Qty : 4, 200,200,200,200,100 ( Total of This is 904) .. And i have Against the order 904 .. total delivered qty i.e 753 .  Means my pending qty is 151 to fulfill 904 order.

So,

Order Qty    Total Delivered Qty      Exp                          Delivered Line Wise Item Qty                Pending Qty

    4                        753                 ( 4 < 753 ) (753 - 4 = 749 )                      4                         0 

    200                    753               ( 200< 749 ) (749 - 200 =549)                 200                0  ( For 200 Order QTy)

    200                   753                 (200<549) ( 549 -200 = 349)                  200                0 ( for 200 order qty)

   200                    753                 (200<349)(349-200 = 149 )                   200                 0 ( for 200 qty )

  200                    753           (200 > 149 )   ( it should consider 0)            149               51 ( 200-149 =51 Pending qty)

100                       753              (100>0 )      ( it should consider 0)            0                  100 ( Pending entire 100 qty)

[Anonymous]

sorry, but for we can not know how much ist delivered per line (Date , CustomerID) ...

I think the community is the wrong place for clarifying the customer needs... that is your task

We can assist you realizing your clearly defined needs in QlikView.