Rolling Inventory/Stock

[niranjana]

Hi All,

Greetings!

I have Date dimension in Qliksense straight table, and measures are Production, Sales and Consumption of Stock Material for Inventory in metric tons....

I am getting all values correct. For opening stock column, I have written the formula in the table expression as 

if(Date=min(total Date), Sum([Initial Stock (MT)])
,
RangeSum(
Above((
Sum([Initial Stock (MT)]) + Sum([VCM Arrival (MT)])
- Sum([Consumption SPVC (MT)])
- Sum([Consumption PPVC (MT)])),
1,
RowNo()
)
))

which is giving correct values.. But, I need to execute the same in Qlik backend script. I tried this :

If(

RowNo() = 1,

[VCM Arrival (MT)] - [Consumption SPVC (MT)],

Peek('Final_Stock') + [VCM Arrival (MT)] - [Consumption SPVC (MT)]

) as Final_Stock

But its giving me all 0 values for Final_Stock...... 

The logic for Current Stock (Opening stock column) is : for first day, Current Stock = Initial stock, from next day onwards, Stock for current date = [Previous day Stock + Previous Day VCM Arri (if it exists)] - (Daily consumption of previous day)

Kindly Help!!

Thanks in advance!!

Niranjana

[rubenmarin]

Hi, using '+' or '-' can break the logic if some field has a null value, RangeSum() will add zero when some value is null, so also you don't need to check for the first row. It could be just:

RangeSum(Peek('Final_Stock'),[VCM Arrival (MT)],-[Consumption SPVC (MT)]) as Final_Stock

[niranjana]

Hi ,

Thanks for your reply, but it is not working. I will try to make more clear. Below, I have attached the screenshot. The column "Opening Stock" is calculated  in chart expression :

if(Date=min(total Date), Sum([Initial Stock (MT)])
,
RangeSum(
Above((
Sum([Initial Stock (MT)]) + Sum([VCM Arrival (MT)])
- Sum([Consumption SPVC (MT)])
- Sum([Consumption PPVC (MT)])),
1,
RowNo()
)
))

I am getttig correct values. I must get same values in next column  named as "Final Stock" for which I used the following script :

NoConcatenate

Temp:

Load
Flag,
Date,
"Production SPVC (MT)",
"Sales SPVC (MT)",
"SPVC NR (USD/MT)",
SPVC_Sales,
"Production PPVC (MT)",
"Sales PPVC (MT)",
"PPVC NR (USD/MT)",
PPVC_Sales,
MonthYear,
"Consumption SPVC (MT)",
"Consumption PPVC (MT)",
[SPVC_VC_Pdn],
[PPVC_VC_Pdn],
[SPVC_FE_Pdn],
[PPVC_FE_Pdn],
"VCM Arrival (MT)",
"Arrival Landed Price (USD/MT)" ,
"Initial Stock (MT)",

If(Date = $(vMinDate),
[Initial Stock (MT)],

RangeSum(peek('Final_Stock'),peek([VCM Arrival (MT)]),-peek([Consumption SPVC (MT)]),- peek([Consumption PPVC (MT)]))) as Final_Stock

resident Final order by Date asc;

drop table Final;

Logic is : for first date , Final Stock is "Initial Stock" which appears correct. From next day onwards, it is :

prev day Final Stock + prev day VCM Arrival - (prev day Daily Consumption)

Kindly Help..

Thanks in advance!!

Niranjana