Cumulative stock value per day

[jorgen_santberg]

Hi there,

I'm having trouble getting the right script to calculate my stock value per product per day from all the past days till this day. Plus I must be able to select a day in the past and the correct stock value of that day must be shown in a text box and I need to store the data in a table.

I have a lot of different transactions, so on a single day there can be many mutations, but basically there is only one field for stock value (Stock_value). My data looks like this:

Stock_value	Date	Units	Item_No	Warehouse	Product_Version
-1.309	31-12-2023	-29	602483	A	1
-3.149	31-12-2023	0	602483	A	1
-326	31-12-2023	-2	602483	A	2
-	31-12-2023	0	602483	A	1
-	31-12-2023	0	602483	A	1
-20	31-12-2023	0	602483	A	1
90	31-12-2023	2	602483	B	2
90	31-12-2023	2	602483	A	1
677	31-12-2023	15	602483	A	3
-1.286	29-12-2023	-2	602483	A	1
-	29-12-2023	0	602483	A	2
-	29-12-2023	0	602483	A	2
1.194	29-12-2023	0	602483	A	2
-2.571	28-12-2023	-4	602483	A	1
-	28-12-2023	0	602483	A	1
-	28-12-2023	0	602483	A	1
2.389	28-12-2023	0	602483	A	1
-	28-12-2023	0	602483	A	2
-	28-12-2023	0	602483	A	2

And my script is:

LET STARTDATE = '01-05-2018'; 

LET ENDDATE = Today();

Let vMinDate = Num(STARTDATE);

Let vMaxDate = Num(ENDDATE);

For i = $(vMinDate) to $(vMaxDate)

Temp_Stockvalue:

LOAD 

$(i)&'|'&"Item No_" as tmp_key,

date($(i)) as Stock_Date, 

     "Item No_" as Product_No, 

      $(i)&'|'&"Item No_"&'|'&Product_Version&'|'&Warehouse as ID,

     Sum(Stock_value) as Stock_Value,

     Sum(Units) as Units

FROM "data_source".qvd(qvd)

where    date([Date]) <=  date($(i)) 

Group by

$(i),

"Item No_",

Warehouse,

Product_Version;

NEXT

store Temp_Stockvalue into ..\QVD\Stock_Value.QVD (qvd);

This way I store a table containing my stock value and stock in units per day and item.

However, I am not getting the results I would expect, so any advise would be greatly appreciated!

[maxgro]

Try with

// read your data

R:

LOAD Stock_value, 

     Date, 

     Units, 

     Item_No, 

     Warehouse, 

     Product_Version

FROM

[https://community.qlik.com/t5/App-Development/Cumulative-stock-value-per-day/td-p/2426571]

(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

// add some other data

CONCATENATE (R)

LOAD Stock_value, 

     Date, 

     Units, 

     -100 as Item_No, 

     Warehouse, 

     Product_Version

FROM

[https://community.qlik.com/t5/App-Development/Cumulative-stock-value-per-day/td-p/2426571]

(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

// group by key and sum

S:

NOCONCATENATE

LOAD 

    Date, 

    Item_No, 

    Warehouse, 

    Product_Version,

SUM(Stock_value) AS Stock_value, 

SUM(Units) AS Units

RESIDENT R

GROUP BY 

    Date, 

    Item_No, 

    Warehouse, 

    Product_Version

;

DROP TABLE R;

// cumulate when the key changes (item, warehouse, prod vers; exclude the date)

T:

NOCONCATENATE LOAD

    Item_No, 

    Warehouse, 

    Product_Version,

    Date, 

Stock_value, 

    Units,

    IF(Item_No <> PEEK(Item_No) OR Warehouse <> PEEK(Warehouse) OR Product_Version <> PEEK(Product_Version), 

    Stock_value, 

    Stock_value + PEEK(Total_Stock_value)

    ) as Total_Stock_value,

    IF(Item_No <> PEEK(Item_No) OR Warehouse <> PEEK(Warehouse) OR Product_Version <> PEEK(Product_Version), 

    Units, 

    Units + PEEK(Total_Units)

    ) as Total_Units     

RESIDENT S

ORDER BY 

    Item_No, 

    Warehouse, 

    Product_Version,

    Date

;

DROP TABLE S;