Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

monthly average inventory?

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

15 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

Well, if you can provide a fragment of the source data and what you want to get as a result, someone will probably help.

Anonymous
Not applicable
Author

Hi Andrey,

Thanks for the respose.

Lets consider i am having closing stock at day level and am considering yesterdays closing stock as todays opening stock.

my average inventory fromula =Average of Opening Inventory and the monthly closing inventory for the time period selected


can some one help me with the expression that calculate avg inventory for the period we select.


Thanks in advance.


Regards,

Sadasiva

Anil_Babu_Samineni

When you use

Avg(Inventory) will works even if you select some thing from Filter

OR

Avg({$<Period = >} Inventory)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Thanks for the response Anil.

but my reqirement is different.

For Example: Date Range: 1 Jan - 31st March 2017

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 i need to replicate in an expression.

Regards,

Sadasiva

dwforest
Specialist II
Specialist II

Could use Aggr(), you would have to create a "Stock Days" calendar with the desired dates,

then expr would be something like:

Avg(Aggr(Avg([stock amt]),[Stock Day]))

Anonymous
Not applicable
Author

Hi David,

Thanks for the response.

I am having my inventory stock value from Aug 2005. I will consider my opening stock for 2017 jan 1st as Total inventory stock value till dec 31st 2016. on  jan 31st  2017 my closing stock value is from Aug 2005 to Jan31st 2017.

Based on this i need to write the expression. Please help me on this.

Thanks,

Sadasiva

dwforest
Specialist II
Specialist II

Not quite sure what you are asking.

If you can provide numbers and expected values that is helpful.

Anonymous
Not applicable
Author

Hi David,

Thanks for the response.

I am having my inventory stock value from Aug 2005. I will consider my opening stock for 2017 jan 1st as Total inventory stock value till dec 31st 2016. on  jan 31st  2017 my closing stock value is from Aug 2005 to Jan31st 2017.

To keep it simple i am using the same case.

Let's say my closing stock value till 31st Dec 2016 is 1M, and this will be my Opening stock for 1st Jan 2017.

and let's say my closing stock for 31st jan2017 is 2M, Closing stock for 28th Feb 2017 is 1M and Closing stock for 31st march 2017 is 4M.

In this case my Formula is

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

or Sum((Opening Stock on Jan 1 , Closing Stock on Jan 31 , Closing Stock on Feb 28 , Closing Stock on Mar 31))/(number of months+1)


Then my out put will be (1+2+1+4)/4=2M


Hope this helps to understand my problem.


Regards,

Sadasiva

dwforest
Specialist II
Specialist II

what you need to do exactly depends on the raw data ... ideas below, note typed here but not tested

if the inventory stock amount is recorded on the last of each month as the sum or if you need to calculate the sum for the month.

the first has less steps, and you can use the Aggr

Avg(Aggr(Avg([stock amt]),[Stock Date]))

assuming values are in [stock amt] and [stock date] fields.. if there are daily counts

Avg(Aggr(Avg({$<[Stock Date]={$(MonthEnd([Stock Date])}>}[stock amt]),[Stock Date]))

If the second, you will need to create a calendar that has a Month dimension and listing every day, search in forums for how to create a calendar.

The date field in your calendar should have the same name as the date field for your stock amt, then

Avg(Aggr(Avg([stock amt]),[Month]))