Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
ok.
that's what I wanted.
I thank you very much for your advice.
you have been very kind and professional.
Hello
Are you planning to create Progr and day field in the script? or is this going to be created in the front end only?
it would be very useful to do it in the script
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;
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.
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.
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
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 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?
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.