Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i need to write an expression using Set Analysis to give me the result of the following..
Wastage on CHILLED dept as a % of CHILLED sales.
my fields are :
[Stock Movement Reason Code] - (Waste codes are 3 and 5)
[Dept] - (N is CHILLED)
[StockValue) - (Waste Value)
[SalesValue] - (Sales Value)
I have tried the following but i can't get the correct result...
sum ( {<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<Dept={'N'}>}SalesValue)
Can anyone tell me what is wrong with my expression please?
Hi Chris,
there's nothing syntactically wrong with your expression.. though you would have to apply the same set analysis expression to both StockValue and SalesValue fields.
Try the following expression
sum({<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<[Stock Movement Reason Code] ={3, 5},Dept={'N'}>}SalesValue)
See if this helps.
Rgrds,
Abhinava
Hi, i have got it working, i can't use the same set analysis for the 2nd part as i need to divide it by ALL CHILLED sales regardless of any wastage or not.
Anyway i have it working now (my posted expression worked, the problem was i had a field selected which shouldnt have been)
I am affraid that the end user would get incorrect figures if they make the same mistake as i did.So how would i use the same expression but exclude the selections made on [Stock Movement Reason Code] on the 2nd part of the expression?
use below code
sum ( {1<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<Dept={'N'}>}SalesValue)