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.