6 Replies Latest reply: Jan 13, 2017 8:24 AM by Stanly Johns RSS

    Slow Moving Stock Calculation using Set Analysis

    Stanly Johns

      Hi everyone,

      I am new to Qlik Sense, I'm working on an exercise with inventory stock and I'm facing a problem which I'm not able to find a solution.

      SMS.JPG

      Here I have a fact table Facts which contains inventory data of the company. I want to count calculate the number of slow moving stock item from the above table and display it on a chart.

      The formula for calculating Slow Moving Stock is:

                     

      Slow Moving Stock= [Inventory Days] > 100

      The formula for calculating Inventory days is:

       

      Inventory Days          = [Inventory (for the period of calculation) / COGS (for the period of calculation)] * (Period of Calculation)

       

      1. E.g.: Inventory Days = [Inventory/COGS]*365

       

      I have written set analysis script for calculating Inventory days as given below:

      vInventory=

      ((Sum({<Year=, Quarter=, Month=, Week=, Date={">=$(=Date(Max(Date)-vCalcPeriod))<=$(=Date(Max(Date)))"}>} Inventory ))

      /

      (Sum({<Year=, Quarter=, Month=, Week=, Date={">=$(=Date(Max(Date)-vCalcPeriod))<=$(=Date(Max(Date)))"}>} COGS )))

      *

      $(vCalcPeriod)


      CalcPeriod is the variable used to set the dynamic calculation period for inventory days.


      The problem is that I could not find a solution to calculate the Slow Moving Stock by using this calculated inventory days.

      Slow Moving Stock value is the item which has inventory days greater than 100.

      Can anyone help me please…………………….

      Thank You

      Stanly R Johns