2 Replies Latest reply: Nov 20, 2012 12:04 PM by kathyhciti RSS

    Set Analysis with embedded IF statement

      I have an industry hierarchy in my qvw: Industry -> Product -> Sub Product. I have a drill down dimension in my straight table that is: Industry -> Region -> Sub Region -> Client (or others that mix the geography hierarchy and industry hierarchy)  To find the most specific industry selection, I can do:


      =IF(not isnull([Sub Product]),[Sub Product],
      IF(not IsNull([Product]), [Product], Industry)))


      as an expression in my table. That way if the dimension is Product, the expression will reflect the different products in each row. Otherwise if I have drilled past Product to Region, all rows will reflect the product selected.


      My problem is that I would like to use this most specific industry in my set analysis, doing something like:


      =sum({$<%Franchise={"$(=IF(not isnull([Sub Product]),[Sub Product],
      IF(not IsNull([Product]), [Product], Industry)))"}>} %Rank)


      where %Franchise is one field of an inline table.  If you have any suggestions it would be greatly appreciated.