topic Re: Set analysis in QlikView

Set analysis

francofiorillo — Sat, 16 Nov 2024 04:42:04 GMT

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.

Re: Set analysis

sunny_talwar — Tue, 22 Jan 2019 12:46:13 GMT

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

Re: Set analysis

francofiorillo — Tue, 22 Jan 2019 13:09:33 GMT

it would be very useful to do it in the script

Re: Set analysis

sunny_talwar — Tue, 22 Jan 2019 13:29:44 GMT

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;

Re: Set analysis

francofiorillo — Tue, 22 Jan 2019 14:03:12 GMT

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.

Re: Set analysis

sunny_talwar — Tue, 22 Jan 2019 14:12:27 GMT

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.

Re: Set analysis

francofiorillo — Wed, 23 Jan 2019 18:55:50 GMT

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

Re: Set analysis

francofiorillo — Wed, 23 Jan 2019 18:55:54 GMT

Re: Set analysis

sunny_talwar — Wed, 23 Jan 2019 19:48:14 GMT

@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?

Re: Set analysis

francofiorillo — Thu, 24 Jan 2019 07:22:41 GMT

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.

Re: Set analysis

sunny_talwar — Thu, 24 Jan 2019 15:04:29 GMT

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;

Re: Set analysis

francofiorillo — Fri, 25 Jan 2019 12:59:52 GMT

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