Cumulative stock value per day

jorgen_santberg — Mon, 04 Mar 2024 12:39:00 GMT

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;

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!

Re: Cumulative stock value per day

maxgro — Mon, 04 Mar 2024 16:47:29 GMT

Try with

// read your data

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

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)

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;

topic Re: Cumulative stock value per day in App Development

Cumulative stock value per day

Re: Cumulative stock value per day