3 Replies Latest reply: Sep 21, 2011 9:05 AM by Sunil Chauhan RSS

    Set Analysis help

    Chris Hopkins

      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?

        • Re: Set Analysis help

          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.




            • Set Analysis help
              Chris Hopkins

              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?