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

[rubenmarin]

Hi, when you say 'is not working'... what's not working? ... Maybe is only that it doesn't adds the initial stock? or doesn't shows anything.

Sample attached to cou

If(RowNo()=1
	  ,RangeSum([Initial Stock (MT)],[VCM Arrival (MT)],-[Consumption SPVC (MT)])
	  ,RangeSum(Peek('Final_Stock'),[VCM Arrival (MT)],-[Consumption SPVC (MT)])) as Final_Stock

nt the inital stock.

[niranjana]

Hi,

Thanks for your reply...

I have one more requirement... I need to make more dynamic...

I have calulcated Wt Average in script :

NoConcatenate

Temp_1:
LOAD
Date,

If(subfield(Concat(Flag,','),',',1)='SPVC','SPVC','PPVC') as Flag,


sum(ExcRate)/2 as ExcRate,



Sum("Initial Landed Price (USD/MT)") as "Initial Landed Price (USD/MT)",
Sum("Production SPVC (MT)") as [Production SPVC (MT)],
Sum("Sales SPVC (MT)") as [Sales SPVC (MT)],
Sum("SPVC NR (USD/MT)") as [SPVC NR (USD/MT)],
Sum(SPVC_Sales) as SPVC_Sales,

Sum("Production PPVC (MT)") as [Production PPVC (MT)] ,
Sum("Sales PPVC (MT)") as [Sales PPVC (MT)],
Sum("PPVC NR (USD/MT)") as [PPVC NR (USD/MT)],
Sum(PPVC_Sales) as PPVC_Sales,

Sum("Consumption SPVC (MT)") as [Consumption SPVC (MT)],
Sum("Consumption PPVC (MT)") as [Consumption PPVC (MT)],
Sum([SPVC_VC_Con]) as SPVC_VC_Con,
Sum([PPVC_VC_Con]) as PPVC_VC_Con ,
Sum([SPVC_FE_Con]) as SPVC_FE_Con ,
sum([PPVC_FE_Con]) as PPVC_FE_Con,

Sum("VCM Arrival (MT)") as [VCM Arrival (MT)],
Sum("Arrival Landed Price (USD/MT)") as [Arrival Landed Price (USD/MT)],
max("Initial Stock (MT)") as [Initial Stock (MT)]

Resident Temp Group By Date;
drop table Temp;

Join (Temp_1)

LOAD
Date,

If(RowNo() = 1 ,[Initial Stock (MT)] ,Peek('Closing_Stock')) as Opening_Stock,

RangeSum(
If(
RowNo() =1,
[Initial Stock (MT)] ,
Peek('Closing_Stock')
),

[VCM Arrival (MT)],
-[Consumption SPVC (MT)],
-[Consumption PPVC (MT)]
) as Closing_Stock

Resident Temp_1
Order By Date;

NoConcatenate

Final:
LOAD

*,

Date(monthstart(Date),'MMM YYYY') as MonthYear,

If(RowNo() =1,
[Initial Landed Price (USD/MT)],
(

Opening_Stock * Peek('WtAvgPrice')
+
([VCM Arrival (MT)] * ([Arrival Landed Price (USD/MT)] * 1.0825 +10 ))

)

/
(
Opening_Stock + [VCM Arrival (MT)]
)) as WtAvgPrice

Resident Temp_1;

Drop Table Temp_1;

exit script;

But, I have defined vCustom = 1.0825 and vCost = 10..I need to change them in input box to change values in table dynamically...

I used this expression in chart :

If(
RowNo() = 1,
[Initial Landed Price (USD/MT)],

If(
RowNo() = 2,
above([Initial Landed Price (USD/MT)]),

(
Opening_Stock * Above([WtAvgPrice])
+
[VCM Arrival (MT)] *
(
[Arrival Landed Price (USD/MT)]
* (1 + (($(vCUSTOM))/100))
+ $(vAC)
)
)
/
(
Opening_Stock + [VCM Arrival (MT)]
)
))

I am getting correct values, but its not dynamically changing when I change vCustom in input...
Kindly help...Maybe code can be such a way that variable part alone is caluclated in chart..Above function prevent it perform dynamically...

Thanks in advance !!

[rubenmarin]

Hi, it's hard to follow what is failing, it will be better if you can create a dummy app like mine that demonstrates the issue, so I can check it.