15 Replies Latest reply: Jul 13, 2017 9:17 AM by Sada Siva

monthly average inventory?

Sada Siva Jun 15, 2017 2:38 AM

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

Re: monthly average inventory?

Andrey Khoronenko Jun 19, 2017 3:02 AM (in response to Sada Siva)

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.
Like Show 0 Likes (0)

Actions
- Re: monthly average inventory?
  
  Sada Siva Jun 19, 2017 3:22 AM (in response to Andrey Khoronenko)
  
  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
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: monthly average inventory?
    
    Anil Babu Jun 19, 2017 6:04 AM (in response to Sada Siva)
    
    When you use
    Avg(Inventory) will works even if you select some thing from Filter
    OR
    Avg({$<Period = >} Inventory)
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: monthly average inventory?
      
      Sada Siva Jun 19, 2017 6:28 AM (in response to Anil Babu )
      
      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
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: monthly average inventory?
        
        David Forest Jun 19, 2017 7:56 PM (in response to Sada Siva)
        
        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]))
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        Sada Siva Jun 20, 2017 2:35 AM (in response to David Forest)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        David Forest Jun 20, 2017 11:58 AM (in response to Sada Siva)
        
        Not quite sure what you are asking.
        If you can provide numbers and expected values that is helpful.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        Sada Siva Jun 21, 2017 1:55 AM (in response to David Forest)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        David Forest Jun 21, 2017 8:08 PM (in response to Sada Siva)
        
        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]))
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        Sada Siva Jun 22, 2017 2:12 AM (in response to David Forest)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: monthly average inventory?
        
        David Forest Jun 22, 2017 11:58 AM (in response to Sada Siva)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
Re: monthly average inventory?

Sada Siva Jun 28, 2017 2:37 AM (in response to Sada Siva)

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
Like Show 0 Likes (0)

Actions
- Re: monthly average inventory?
  
  Sada Siva Jul 13, 2017 8:38 AM (in response to Sada Siva)
  
  stalwar1, Sunny can you help me on this?
  
  Regards,
  Sadasiva
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: monthly average inventory?
    
    Sunny Talwar Jul 13, 2017 8:40 AM (in response to Sada Siva)
    
    I can try, but do you have some sample you can share to look at what you have and explain what you need?
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: monthly average inventory?
      
      Sada Siva Jul 13, 2017 9:17 AM (in response to Sunny Talwar )
      
      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
      
      Like Show 0 Likes (0)
      
      Actions