Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kadirozgurcirak
New Contributor II

Set Analysis

Hello

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

        

YearMonthYearNbrMonthNbrPDLLocationIdProductIdStockOnHandStockOnOrderSafetyStockLevelMaxStockLevel
2015120151108420151285128510844201232
2015112015117892015111860186789590749
2015920159105620159200220010562801218
2015820158151120158215121515114001830
20153201531069201534814810695703647
2015720157669201571151115669280518
20159201593992015913801383992801218

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)

kadirozgurcirak
New Contributor II

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?

kadirozgurcirak
New Contributor II

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?

kadirozgurcirak
New Contributor II

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.

sasiparupudi1
Honored Contributor III

Re: Set Analysis

May be like the attached sample?

Community Browser