Problem with using "less than" variable inside set analysis

[alexander_mo]

Hi everyone,

I am currently working on an inventory analysis dashboard and got stuck on a seemingly simple set analysis..

Within the dashboard, I am using variables to calculate certain inventory thresholds per SKU (among others the "safety stock"). These are dynamically calculated using a variable I called $(vSafetyStock). 

Up to here, all inventory calculations work flawlessly. However, I am now trying to put together an area chart that shows the inventory structure on a monthly basis, i.e. the number of SKUs without stock, below their safety stock levels, etc. (see Excel chart below). In doing so, I have tried to embed the above-mentioned variable into a count-function, as you can see below.

Inventory structure by month

=Count({$<Stock_actual = {"<$(=vSafetyStock)"}>} SKU)

I was hoping that Qlik Sense would compare the actual stock to the defined safety stock for each SKU within the current selection and then count for how many SKUs the condition ("actual stock is less than safety stock") is met. Unfortunately, my chart remains emtpy. 

I've looked into many comparable questions on here (e.g. the ones below), but still can't seem to find my mistake.

Greater than or less than a Variable in set analysis 

Less than variable in set analysis 

Set Analysis - how to use variables inside set analysis 

Any help would be greatly appreciated.

Regards,

Alexander

[sunny_talwar · Accepted solution]

Set analysis is evaluated once per chart and it is across the board, and cannot be performed on each SKU.... to check the values which is getting used in the set analysis, use vSafetyStock in a kpi object and you will see what value it is picking to use for the chart. It might very well be null if there are multiple values which the variable outputs based on different sku.

In this case, I suggest using Aggr() function to do what you are looking to do something along the lines of

=Count(Aggr(
  If(Stock_actual < $(vSafetyStock), SKU)
, SKU))