monthly average inventory?

[Anonymous]

Hi, can some one help me to calculate monthly average inventory?

[Anonymous]

Hi David,

Thanks for your valuable time and response.

My case bit tricky here. I am using below expression to get my closing stock at any given point of time.

Lets say i am seeing my closing stock today i.e 22nd June 2017

sum({<[_KEY_DATE]= {"<=$(vMaxYearDate)"}>} [Cost Amount])

This expression will give me the inventory till this date.

2nd i am using

sum({<[_KEY_DATE]= {"<=$(vMinYearDate)"}>} [Cost Amount]) to get Opening stock.

with these two i am using (opening stock+closing stock)/2 to calculate average inventory, which is not giving accurate numbers.

here if i select a month, it will calculate opening stock and closing stock and giving the correct number.

if i select YTD it will take (opening balance on jan1st + closing stock on today)/2 which is not correct. i need to calculate it for every month like sum(opening stock on jan1st,closing stock on 31st jan,closing stock on 28th Feb...)/(num of months+1)

hope you understand my problem.

Regards,

Sadasiva

[dwforest]

This can be done by add 1 in front of the Set Expression so that it ignores filters, this will cause problems if you want other filters to apply, for example location, then you'd have to add something like:

sum({1<[_KEY_DATE]= {"<=$(vMinYearDate)"}> * $<location=>} [Cost Amount]) to get Opening stock.

the actual tables you're working with and sample data would help those trying to help you

[Anonymous]

I am having similar issue like

Average Stock | Qlik Community

kalyandg‌, did you got solution for that, please update it. I am also using the same Average inventory to calculate inventory turns.

Thanks in advance,

Regards,

Sadasiva

[Anonymous]

stalwar1‌, Sunny can you help me on this?

Regards,

Sadasiva

[sunny_talwar]

I can try, but do you have some sample you can share to look at what you have and explain what you need?

[Anonymous]

Hi Sunny,

I am using this formula to get Closing Stock value.

Sum({<[_KEY_DATE]= {"<=$(vMaxYearDate)"},Year=,Month=,Quarter=,YearMonth=,Date=>}

[Cost Amount])

and to calculate Opening stock value,

Sum({<[_KEY_DATE]= {"<$(vMinYearDate)"},Year=,Month=,Quarter=,YearMonth=,StockYearMonth=, Date=>} [Cost Amount])

as of now am using (Opening Stock+Closing Stock)/2 formula to get average inventory.

If i wanna calculate this monthly how can i achieve this.

Avg Inventory = (Average of (Opening Stock on Jan 1 , Closing Stock on Jan 31 , Closing Stock on Feb 28 , Closing Stock on Mar 31))

This was the formula shared by client.

rangesum( above( Sum({<[_KEY_DATE]= {"<=$(vMaxYearDate)"},Year=,Month=,Quarter=,YearMonth=,Date=>}

[Cost Amount]),0,rowno()))

This formula will gives me the all closing stocks by month when i am using Stockmonthyear as dimension.

Regards,

Sadasiva