Display zero and non-zero values in a table

[#1]

Hi

Following is the case description

There are 4 data entities involved in this problem.

1. Analyst

2. Security

3. Fund

4. Benchmark

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?

Set analysis:

Fund Allocation (column 2)

sum(fund_allocation)

Benchmark Allocation (column 3)

sum(benchmark_allocation)

Thanks

[Gysbert_Wassenaar]

You can try adding sum({1} 0). If that doesn't work then it would help if you could post a small Qlik Sense app that illustrates the problem.

[reddy-s]

Hi Hemang,

Did you try the show zero values property in the add-ons properties panel?

Thanks,

Sangram.

[#4]

Hi Sangram

I have Show zero value check box checked.

[#5]

but the output is still the same.

[reddy-s]

Hi Hemang,

There should be something missing in the data model. Is it possible to share the qvf?

Thanks,

Sangram.