8 Replies Latest reply: Aug 4, 2017 1:41 PM by Sasidhar Parupudi

# Set Analysis

Hello

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

 YearMonth YearNbr MonthNbr PDL LocationId ProductId StockOnHand StockOnOrder SafetyStockLevel MaxStockLevel 20151 2015 1 1084201512851 285 1084 42 0 12 32 201511 2015 11 7892015111860 186 789 59 0 7 49 20159 2015 9 1056201592002 200 1056 28 0 12 18 20158 2015 8 1511201582151 215 1511 40 0 18 30 20153 2015 3 106920153481 48 1069 57 0 36 47 20157 2015 7 669201571151 115 669 28 0 5 18 20159 2015 9 399201591380 138 399 28 0 12 18

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?

• ###### 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)

• ###### 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?

• ###### 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?

• ###### 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.

• ###### Re: Set Analysis

May be like the attached sample?