14 Replies Latest reply: Feb 3, 2016 3:18 PM by Paul Scotchford RSS

    Set Analysis in Table

    Hemang Dave

      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

        • Re: Set Analysis in Table
          Paul Scotchford

          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.

            • Re: Set Analysis in Table
              Hemang Dave

              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

            • Re: Set Analysis in Table
              Sangram Reddy

              Hi Hemang,

               

              is it possible to attach a sample QVF?

               

              Thanks,

              Sangram.

              • Re: Set Analysis in Table
                jagan mohan rao appala

                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


                  • Re: Set Analysis in Table
                    Hemang Dave

                    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

                      • Re: Set Analysis in Table
                        Rob Wunderlich

                        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

                          • Re: Set Analysis in Table
                            Hemang Dave

                            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

                              • Re: Set Analysis in Table
                                jagan mohan rao appala

                                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.

                                  • Re: Set Analysis in Table
                                    Hemang Dave

                                    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.

                          • Re: Set Analysis in Table
                            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 .

                              • Re: Set Analysis in Table
                                Hemang Dave

                                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.

                                  • Re: Set Analysis in Table
                                    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)