4 Replies Latest reply: Apr 28, 2011 6:28 PM by Matt Benson RSS

    If Statement and Set Analysis

      Hi I have two problems but probably only need one of them solving.

      I am trying to add sets of costs based on a product group, 2 product groups fall into the same category and all the rest in the second category.

      Previously I created expressions for all the individual costs and then used an IF statement to add them together such as

      =IF([ProductGroup] ='023' or [ProductGroup]='025', [COST1]+[COST2], [COST3]+[COST4])

      where [COST1] , [COST2] etc are expressions within the chart.

      This works fine for individual lines but for the partial sum I get the sum of the else clause ie just [COST3]+[COST4]

      If I use set analysis and try enter [COST1] , [COST2] etc I get Bad Field name so assume I can't use an expression within the set analysis?


      As I say line by line this works with the if Statement but not for the total. Any ideas much appreciated.





        • If Statement and Set Analysis
          Erich Shiino

          You can not use chart expressions inside set analysis.

          If you are using straight table you can set the expression to be sum of lines intead of expression total (check on the right side of the tab 'Expressions' when you select each expression.

          Maybe you could rewrite the expressions to have a total that makes sense.

          if COST1= sum(COST1), your set analysis would be sum( {<[ProductGroup]={'023','025'}>} COST1) and so on...

            • If Statement and Set Analysis



              Many thanks for your quick reply. Unfortunately COST1 isn't quite that straightforward it looks more like the below so with several to add in it's going to get messy in the Set Analysis.






              (Measured='Measured' and (only(left([AccountNo],4))='A900' or only(left([AccountNo],4))='S001'),sum(QTY * NotionalCostMeasured * LINK_TO_STOCK), if(StockCode='1307',(sum(CCost)/LINK_TO_STOCK),sum(CCost))) + sum(QTY * [NAVNotionalCost]