Slow Moving Stock Calculation using Set Analysis

[stanlyrj]

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.

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

[petter]

If you provide use with some test data it would be so much easier for us to help you out ...

[mkelemen]

Assuming that the vInventory formula works as expected,

this should give you only those that have Inventory Days > 100

=If($(vInventory) > 100, $(vInventory), Null())

  Matus

[stanlyrj]

Hi Petter,

Thanks for replying, I have included a sample data.xlsx file Dropbox please refer to the below link

https://www.dropbox.com/s/conb0ygdrkocmyd/Sample%20Data.xlsx?dl=0

Thank You

[stanlyrj]

H Matus,

Thanks for replying.Here I want to count the items which are having Inventory days greater than 100 And I want to display the count in a KPI object. I have tried your script its gives  the inventory days greater than 100 days. Could you please suggest me a solution to count the items based on the above mentioned condition.

Thank You

[mkelemen]

There is probably a more elegant solution but this should work

=Sum($(vInventory) > 100, 1, 0)

  Matus

[stanlyrj]

Hi Matus,

Thank you so much for you reply. I'll try your script.

Thank You