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]

Are you planning to create Progr and day field in the script? or is this going to be created in the front end only?

[francofiorillo]

it would be very useful to do it in the script

[sunny_talwar]

Try this script

Table:
LOAD * INLINE [
    Item, Data, IN, OUT
    A, 1/1/18, 5000, 
    A, 10/3/18, , 700
    A, 1/4/18, , 600
    A, 3/4/18, , 800
    A, 6/4/18, , 100
    A, 14/4/18, 600, 
    A, 2/5/18, , 100
    A, 26/5/18, , 300
    A, 2/6/18, 300, 
    A, 16/6/18, , 150
    A, 1/1/19, , 
    A, 10/2/19, , 700
    A, 3/3/19, , 600
    A, 30/3/19, , 800
    A, 6/4/19, , 100
    A, 14/4/19, 600, 
    A, 2/5/19, , 100
    A, 26/5/19, , 300
    A, 2/6/19, 300, 
    A, 16/6/19, , 150
];

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;

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]

It seems to me that this is what I want.
you are great !!!! thank you.
I ask you something else.
it happens that at the same date it has two distinct movements that I should combine first before counting the days.
do I have to do a group by?
Meanwhile, I thank you for the support.
I'm really grateful.

[sunny_talwar]

I guess depending on what exactly you want, you can def. use Group By to combine multiple Item and Data combination before you create Progr and day fields.

[francofiorillo]

I'm sorry but I have not solved yet.
I send you the qvw in which I have uploaded data more consistent with reality.
First, the day difference must be applied to the line with the smallest date. I want to know how many days that quantity of that item remains in stock.
but the thing that I just can not do is reported as an example with the 03/03/2019 date for Item A, while for the Item BBB is reported with the date 20/03/2019.
I want them to become one line:
A - 3/3/2019 - 4 - 605
BBB - 20/03/2019 -125 - 10
just so I can calculate the days correctly.
give me another little big help?
thank you

[francofiorillo]

I'm sorry but I have not solved yet.
I send you the qvw in which I have uploaded data more consistent with reality.
First, the day difference must be applied to the line with the smallest date. I want to know how many days that quantity of that item remains in stock.
but the thing that I just can not do is reported as an example with the 03/03/2019 date for Item A, while for the Item BBB is reported with the date 20/03/2019.
I want them to become one line:
A - 3/3/2019 - 4 - 605
BBB - 20/03/2019 -125 - 10
just so I can calculate the days correctly.
give me another little big help?
thank you

[sunny_talwar]

---

@francofiorillo wrote:

 thing that I just can not do is reported as an example with the 03/03/2019 date for Item A, while for the Item BBB is reported with the date 20/03/2019.
I want them to become one line:
A - 3/3/2019 - 4 - 605
BBB - 20/03/2019 -125 - 10

but they are different Items with different dates, why do you want them to be on a single row? I am not sure I understand it completely? May be you need to use MonthYear as dimension instead of date?

[francofiorillo]

maybe I was not clear.

I want the result that I report in the attached "Result" table, starting from the initial Table.

I need to keep the "IN" and "OUT" values distinct for each date.