Set Analysis

[Anonymous]

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?

[sunny_talwar]

Try this

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

[sunny_talwar]

Or create a flag in the script

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

and then this:

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

[Anonymous]

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.

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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]

May be like the attached sample?