inventory of a product based on the orders

[Navars]

Hi All,

I want to calculate the Inventory of the products based on its orders.

eg. in the source file, product P1 has total units has 30, its first order (Order id 1) has ordered 15 units so the remaining units of Prod P1 has 15, we call it as the order is completed. (30-15=15)

in the second-order  (Order id 2) it ordered 20 units, but we have 15 units only (15-20 =-5) partially completed.

in the third-order (Order id 3) it ordered 15 units, but we don't have units to give hence we canceled the order.

Thanks in advance.

Sra1

[kaushiknsolanki · Accepted solution]

Hi,

Try this code.

Data:
LOAD prodid,
ordid,
[noof orders],
[total units],
AutoNumber(RowNo(),prodid) as Rownum
FROM
[C:\Users\Kaushik\Downloads\inventory.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left join (Data)
Load prodid, Sum([total units]) as [Overall Units]
Resident Data Group by prodid;

Data1:
Load *,If([Remaining Units]>0,'Completed',If([Remaining Units]<0,'Partial Completed','Cancelled')) as Status;
Load *,If(Rownum=1,[Overall Units]-[noof orders],If(peek([Remaining Units]) <=0,0,peek([Remaining Units])-[noof orders])) as [Remaining Units]
Resident Data Order by prodid,ordid;

Drop table Data;

Output is as shown below

[Navars]

Hi Kaushik,

Thanks for the reply.

by using your logic I am getting the expected result. but in one case Status is not populating properly.

product P2 has total units 25 and order received 25, it supposed to show as completed status and remaining units as zero. but its showing Cancelled as the status.

I have attached the sample data, need to check the Product P2 for the above scenario.

Thanks,

Sra1

[Navars · Accepted solution]

to get the correct statues i used below one.

if(remaining>0 or (remaining=0 and Above(remaining)=orders), 'order completed',

if(remaining<0 and Above(remaining)=0,'closed',

if(remaining<0, 'Partially completed',

)))

and i achieved the same output in frontend by using the below expression.

=if(aggr(RowNo(),prodid,ordid)=1, [units]-[orders],

if(aggr(RowNo(),prodid,ordid)>1,above(remaining)-orders)

)