QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner

Set analysis

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.

Labels (2)

• Set Analysis

2 Solutions

Accepted Solutions
MVP

Re: Set analysis

Try this script

```Table:
Data,
Sum(IN) as IN,
Sum(OUT) as OUT
Group By Item, Data;
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:
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:
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;```
Partner

Re: Set analysis

ok.
that's what I wanted.
you have been very kind and professional.
Hello

11 Replies
MVP

Re: Set analysis

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

Partner

Re: Set analysis

it would be very useful to do it in the script

MVP

Re: Set analysis

Try this script

```Table:
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:
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:
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;```

Partner

Re: Set analysis

It seems to me that this is what I want.
you are great !!!! thank you.
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.

MVP

Re: Set analysis

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.

Partner

Re: Set analysis

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

Partner

Re: Set analysis

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

MVP

Re: Set analysis

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

Partner

Re: Set analysis

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.