Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis in Table

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

14 Replies
paul_scotchford
Specialist
Specialist

Try removing the table. references prefixing your predicate fields and observe any effects.

Your syntax looks correct, I replicated it (with my own fields on an app of different data subject) and

it worked fine.

reddy-s
Master II
Master II

Hi Hemang,

is it possible to attach a sample QVF?

Thanks,

Sangram.

jagan
Luminary Alumni
Luminary Alumni

HI,

Try changing this portion of the expression

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


To

[rating_history.contact_id] = {'$(=MaxString([rating_history.contact_id]))'}


Regards,

Jagan


Not applicable
Author

Hi

Thanks for the revert.

Tried changing the code as you suggested but the problem still persists. I think the field rating_history.contact_id is working fine, the issue is when I place the code that worked in a text box into table it is referencing the account id of the row dimension (i.e. 1st column that's titled Fund) so as the fund name changes in each row it is not able to ignore the fund ids but rather use the fund it which is hard coded in the code (id account_id '8849')

Any other suggestion to fix this?

Thanks

-Hemang

Not applicable
Author

Hi Paul

Thanks for the revert. Tried changing the Table. references. Unfortunately it doesn't seem to work.

I think the issue is when I place the code that worked in a text box into table it is referencing the account id of the row dimension (i.e. 1st column that's titled Fund). So as the fund name changes in each row it is not able to ignore the fund ids but rather use the fund it which is hard coded in the code (i.e. account_id '8849')


Any other suggestion?


Thanks

Not applicable
Author

Hi Sangram

Unfortunately, the QVF is too big to share.

Thanks

paul_scotchford
Specialist
Specialist

Could you do a limited load for the QVF and perhaps obfuscate the data if it is confidential, I wouldn't mind having a closer look at the issue.

cheers

Paul

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Works in a dimensionless text box, but not in a table that has a dimension. You'll want to add the TOTAL keyword to ignore  the dimension value.

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'}

         >}

TOTAL contrib_mktval_weight)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable
Author

Thanks Rob

That was very helpful. Following is the output.


pic1.png

Lastly I need to make the benchmark% dynamic i.e. Display Benchmark% corresponding to the Fund in the 1st column.

Note that currently the benchmark id is hard coded so it is showing as if the benchmark is constant for each fund. In reality benchmark varies depending on the Fund.

In order to achieve this I've written the following set analysis. Basically, making account id dynamic depending on the fund in the 1st column.

Set Analysis:

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

         account_id = {"only({1 < parent_clone_type={'Parent'}, [rating_history.contact_id] = {$(=[rating_history.contact_id])}, effective_date={"$(=date(max(effective_date),'YYYY-MM-DD'))"}, [rating_history.end_date] = {'3999-12-31'}>} benchmark_account_id)"},

         [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 contrib_mktval_weight)

For some reason it is breaking.

pic2.png

Could you please guide as to what i am doing wrong here?


Thanks