Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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 .
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.
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.
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)