Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this
Count(DISTINCT {<ProductId = {"=StockOnHand>MaxStockLevel"}>} ProductId)
Or create a flag in the script
LOAD If(StockOnHand>MaxStockLevel, 1, 0) as Flag
and then this:
Count(DISTINCT {<Flag = {1}>} ProductId)
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.
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?
StockOnHand and MaxstockLevel stored in one table but ProductID is stored also in another table.
But ProductID is used for categorization of products like :
I think these wont effect outcome of the formula
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?
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.
May be like the attached sample?