Set Analysis with embedded IF statement

[#1]

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.

[christian77]

Hi.

Set for not is null

Franchise={'*'}

That means, it has to have a value, therefore is not null.

You may use it.

[#3]

I want the Franchise selection to be the lowest level industry selection, not just any value.