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