Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

Set Analysis


I have a table with + million rows. Here is a brief example of table :



My aim is to count unique product id which has overstock value as StockOnHand is greater than MaxStockLevel

I can do this with if formula like : count(distinct(if(StockOnHand>MaxStockLevel, ProductId)))

But i want to calculate this as a set analysis.

Is it achievable?

Tags (1)
8 Replies

Re: Set Analysis

Try this

Count(DISTINCT {<ProductId = {"=StockOnHand>MaxStockLevel"}>} ProductId)

Re: Set Analysis

Or create a flag in the script

LOAD If(StockOnHand>MaxStockLevel, 1, 0) as Flag

and then this:

Count(DISTINCT {<Flag = {1}>} ProductId)

New Contributor III

Re: Set Analysis

Thanks for quick reply bu it returns as 2. Correct answer is 807.

And i couldnt understand ProductId = {"=StockOnHand>MaxStockLevel"}

I have knowledge to do that with flags but im trying to calculate it in set analysis without using flags.

Re: Set Analysis

Do you have these fields stored in more than one table?

ProductId or StockOnHand or MaxStockLevel

or do they all come from one table in the backend?

New Contributor III

Re: Set Analysis

StockOnHand and MaxstockLevel stored in one table but ProductID is stored also in another table.

But ProductID is used for categorization of products like :

  1. CCTV
    1. Cameras
      1. Lenses
        1. 3MP

I think these wont effect outcome of the formula

Re: Set Analysis

I am not 100% sure, but this might be causing set analysis to not work because ProductId is in another table... can you check this for the sack of testing to add ProductId and the other two tables in a single table and then use the set analysis to check if it works or not?

New Contributor III

Re: Set Analysis

Ok i will check that but lets think that i have onnly above table as source file.

So when i calculate in excel count of distinct product ids with StockOnHand > MaxStockLevel, it returns as 807.

With if formula, there is no problem but with set analysis i cant find any logical result.

Honored Contributor III

Re: Set Analysis

May be like the attached sample?