Following is the case description
There are 4 data entities involved in this problem.
5. Allocation value
Data entity relationships:
An analyst is associated to more than one securities.
A security can be associated to a fund, a benchmark, both or none.
-- A security can be associated with more than one funds and benchmarks.
-- Each security has a corresponding allocation value associated with it.
A fund can either have a benchmark or not have a benchmarks associated with it.
-- A fund consists of securities and so does a benchmark.
-- A security can be part of a fund, a benchmark or both.
-- Each security has a corresponding allocation value associated with it. The value can be zero or greater than zero.
A benchmark can be associated with one or more funds.
-- A Benchmark consists of securities
Allocation Value is associated to securities.
-- The allocation value to a security varies from fund to fund and from benchmark to benchmark. i.e. allocation of a given security is different in fund A compared to fund B as well as benchmark A1 to benchmark B1.
-- The allocation value of a security within a fund can be zero or greater than zero.
-- The allocation value of a security within a benchmark can be zero or greater than zero.
Action: User selects an analyst. A table needs to display:
Column 1: List of only those funds where the securities associated with the selected analyst are either part of the fund, part of the benchmark or both associated to the fund. i .e. don't display funds if none of the securities associated with the selected analyst are part of either the fund or the benchmark.
Column 2: Sum of security allocations within each fund (security roll up to fund) for only those securities that are associated with the selected analyst.
Column 3: Sum of security allocations within each benchmark (security roll up to benchmark) for only those securities that are associated with the selected analyst.
Problem: If the sum of security allocations for the selected analyst is zero, it still needs to displayed in that fund in the table as long as the sum of those same securities within a benchmark is greater than zero.
Using the following expression in set analysis I am able to display list of funds where the sum of securities associated with the selected analyst are greater than zero but if the security is part of the benchmark and not the fund it is not displaying those funds.
I am also setting following rule in the fund column setting.
Any thoughts what needs to be done differently in the set analysis?
Fund Allocation (column 2)
Benchmark Allocation (column 3)