Set analysis

[francofiorillo]

Hello
I would need help, please. These are days that I can not solve.
I have a database of all the warehouse movements related to the items in stock.
For each item I have a situation as reported in TAB1 (load the table with a Order By Item and Date).
First of all I have to know the existence at the date (DD / MM / YY) of each item.
For example, on 2/5/18 the value, for Item A, is equal to 3300.
I must do this.
First I choose two dates, for example: 2/5/18 and 30/3/19
Starting from 2/5/18, I have to calculate the days of stock of each <progr> (value to be calculated starting from the first movement in the table) and make the sum up to 30/3/19. Also I must add the Column (Progr * day)

Finally I have to apply the following formula:
 ([sum of OUT] * [sum of Day]) / [sum of Progr * Day]
With the example data will be: (2650 * 339) / 1008000 = 0.89
Obviously, the items in a warehouse are many, on the same date there may be more movements for the same item and the user can choose the two dates as desired.

Thanks in advance for the help you want to give me.

[sunny_talwar · Accepted solution]

Try this script

Table:
LOAD Item,
	 Data,
	 Sum(IN) as IN,
	 Sum(OUT) as OUT
Group By Item, Data;
LOAD * INLINE [
    Item, Data, IN, OUT
    A, 20/06/2019, , 150
    A, 10/06/2019, 300
    A, 05/06/2019, , 300
    A, 30/05/2019, , 100
    A, 25/05/2019, 600
    A, 20/05/2019, , 100
    A, 30/03/2019, , 800
    A, 03/03/2019, , 5
    A, 03/03/2019, , 600
    A, 03/03/2019, 4
    A, 10/02/2019, , 700
    A, 01/01/2019, , 100
    A, 16/06/2018, , 150
    A, 02/06/2018, 300
    A, 25/05/2018, , 300
    A, 02/05/2018, , 100
    A, 14/04/2018, 600
    A, 06/04/2018, , 100
    A, 03/04/2018, , 800
    A, 01/04/2018, , 600
    A, 10/03/2018, , 700
    A, 02/02/2018, 5000
    BBB, 20/06/2019, , 150
    BBB, 08/06/2019, 100
    BBB, 02/06/2019, , 300
    BBB, 30/05/2019, , 100
    BBB, 25/05/2019, 600
    BBB, 20/05/2019, , 100
    BBB, 30/03/2019, , 800
    BBB, 20/03/2019, 25
    BBB, 20/03/2019, , 10
    BBB, 20/03/2019, 100
    BBB, 10/02/2019, , 700
    BBB, 01/01/2019, , 100
    BBB, 16/06/2018, , 150
    BBB, 02/06/2018, 100
    BBB, 25/05/2018, , 30
    BBB, 02/05/2018, , 100
    BBB, 14/04/2018, 200
    BBB, 06/04/2018, , 100
    BBB, 03/04/2018, , 40
    BBB, 01/04/2018, , 600
    BBB, 10/03/2018, , 70
    BBB, 02/02/2018, 10000
];

TempTable:
LOAD *,
	 If(Item = Previous(Item), RangeSum(Peek('CumIN'), IN), IN) as CumIN,
	 If(Item = Previous(Item), RangeSum(Peek('CumOUT'), OUT), OUT) as CumOUT,
	 RangeSum(If(Item = Previous(Item), RangeSum(Peek('CumIN'), IN), IN), -If(Item = Previous(Item), RangeSum(Peek('CumOUT'), OUT), OUT)) as Progr
Resident Table
Order By Item, Data;// desc;

FinalTable:
LOAD *,
	 If(Item = Previous(Item), Previous(Data) - Data, 0) as day
Resident TempTable
Order By Item, Data desc;

DROP Table Table, TempTable;
DROP Fields CumIN, CumOUT;

[francofiorillo · Accepted solution]

ok.
that's what I wanted.
I thank you very much for your advice.
you have been very kind and professional.
Hello