Set Analysis in Table

[#1]

Hi

I've a table containing Fund, Fund Name, Fund% and Benchmark% columns.

Data Relationships:

Fund consists of securities,

Securities are covered by an analyst,

Funds have a distinct benchmark associated to it,

Benchmark consists of securities.

Problem:

--> Based on the user selected analyst on the front end, display Fund% for each of the funds in a table.

--> Each row in the table displays a fund, Fund % and Benchmark %

--> Fund % is the aggregate total % of all those securities that are part of the fund as and at the same time covered by the selected analyst (Both conditions have to satisfy).

--> Benchmark% is the aggregate total % of all the securities that are part of the benchmark corresponding to the fund and at the same time covered by the selected analyst (Both conditions have to satisfy).

I am able to get the Fund% column computed correctly both separately in a text box and also in the table. However, for the benchmark, while it is working fine in a text box I breaks when I put the same code in the table!

Following is the set analysis used:

For Fund%

Sum({1 < parent_clone_type={'Parent'},

               account_type_trp=-{'INDEX', 'MODEL'},

               [rating_history.contact_id] = {$(=[rating_history.contact_id])},

               effective_date={"$(=date(max(effective_date),'YYYY-MM-DD'))"},

              [rating_history.end_date] = {'3999-12-31'}

         >}

contrib_mktval_weight)

For Benchmark%

Sum({1 < account_id = {'8849'},

                [rating_history.contact_id] = {$(=[rating_history.contact_id])},

                effective_date={"$(=date(max(effective_date),'YYYY-MM-DD'))"},

                [rating_history.end_date] = {'3999-12-31'}

         >}

contrib_mktval_weight)

Any idea what I am going wrong?

Thanks

[jagan]

HI,

Try like this

Sum({1 <

                [rating_history.contact_id] = {$(=[rating_history.contact_id])},

                effective_date={"$(=date(max(effective_date),'YYYY-MM-DD'))"},

                [rating_history.end_date] = {'3999-12-31'}

         >}

TOTAL <Fund> contrib_mktval_weight)

Regards,

Jagan.

[paul_scotchford]

Why is this required [rating_history.contact_id] = {$(=[rating_history.contact_id])}  ?

Seems to be SELF = SELF , just curious, I really would like to look at the .qvf and model .

[#4]

Hi Paul

rating_history_contact_id represents a unique identifier for an Analyst.

[rating_history.contact_id] = {$(=[rating_history.contact_id])} This line of code is used to pass the analyst code that the use selects on the front end.

[#5]

Hi Jagan

Tried TOTAL <Fund> contrib_mktval_weight

unfortunately it didn't cut it.

The issue is retrieving correct benchmark id corresponding to each fund and assigning it to account_id in the code

so basically coding in a way that it reads benchmark_Id for the row dimension. i.e. in benchmark% it should retrieve the benchmark% corresponding to the fund in each row.

[paul_scotchford]

Is selection of the Analyst is from a Filter object (not that it matters where selection happens)? Because qlik uses an associative model I would have thought the id = id line is not required in the predicate as the Set Analysis would work across the selected dimension(s) , of course the {1} negates the user selection. A long shot without seeing the model et al, but try ...

Sum({$ <  account_id = {'8849'},

                effective_date={"$(=date(max(effective_date),'YYYY-MM-DD'))"},

                [rating_history.end_date] = {'3999-12-31'}

         >}

contrib_mktval_weight)