15 Replies Latest reply: Aug 6, 2012 4:19 AM by Linda Diodato RSS

    Dynamic listbox

    Linda Diodato

      Hi,

       

      I have a list of Clients which is loaded twice in my script, once as Client and once as C_Client.

       

      In my app, the Clients are loaded in separate list boxes based on the nr of Partners they have been served from in a given amount of time. At the moment, the list of Clients served by 0 Partners is has the following calculated dimension:

      =if(aggr(count( distinct Partner)=0,  Client), Client)

       

       

      I need to create a new list box made of all the C_Clients whose name match all the Clients that fall in the category above. I've tried the following calculated dimension but it doesn't work: =if(C_Client=if(aggr(count(distinct Partner)=0,  Client), Client), C_Client).

       

      Can somebody help please?

       

      Thanks!

        • Re: Dynamic listbox

          I don't think you need to have Clients loaded twice for this. Once would be enough. You'd have 2 listboxes though, one with your calculated dimension and the other just "normal". The one with the calculated dimension should then "affect" the other so that the 2nd one should show the clients of the 1st

            • Re: Dynamic listbox
              Linda Diodato

              The reason I need to load it twice is that one of lists should be totally separated from any Partner data that i have in my app and this is the only way I can achieve what I need by 100%.

              Can you please help with the expression?

               

              Thanks

                • Re: Dynamic listbox
                  Stefan Wühl

                  Could you post a small sample app?

                   

                  Or try maybe something like

                   

                  =aggr(if(C_Client = if(count(distinct Partner)=0,Client),C_Client, C_Client, Client)

                   

                  or

                   

                  =aggr( only({<C_Client = p({<Client = {"=count(distinct Partnert)=0"}>} Client) >} C_Client), C_Client)

                    • Re: Dynamic listbox
                      Linda Diodato

                      The above didn't work I'm afraid.

                       

                      Here js the thing:

                      I have calculated three 'sub-sections' of Client (Green, Amber, Red) and although Green and Amber are ok, the problem is that whenever I make selections on fields related to Sales - i.e. Year, Partner, etc - some Green and Yellow will disappear because they don't match the conditions anymore and none of the Red will show.

                      What I want is the C_Client list I'm trying to build to show me all the Clients that, based on the current selections, don't match the Green or Yellow conditions, in a few words the C_Clients that have the same name as the Clients with 0 Partners. So ideally, in the attached example, if nothing is selected, the C_Client listbox should show the 3 Red Clients whereas if I select for example Years 2010 and 2011, C_Client show show the following:

                       

                      JAI JBAM GCPDYNE XGQATLHH (PGHDESS) KYOTQZG / Ihotgke

                      K.N. SJVNTY DSWJTKOHCDR EPS / Zbanlfl

                      MHGLZ RÜFGX BRYO & WY FQ / Esusrrf

                      NWEVS SVGPOAN / Ynummhu

                       

                      Equally, once this is solved, the Red slice in the pie chart should really have the sum of C_Opportunity for all the C_Clients whose name is the same as the Clients with 0 Partners.

                       

                       

                      Hope this explains it all.

                       

                      Thank you so much for helping me with this

                       

                      Linda

                        • Re: Dynamic listbox
                          Stefan Wühl

                          I've got both above expressions working after correcting small typos. But the first one is linking your two Client fields when selecting in C_Client, so this is probably not what you want.

                           

                          This expression:

                          =aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client) >} C_Client), C_Client)

                           

                          works on my side and is reproducing the "red" Client table. But I was not able to reproduce your results when selecting years 2010 and 2011, even the "red" Client table is showing nothing.

                           

                          Regards,

                          Stefan

                            • Re: Dynamic listbox
                              Linda Diodato

                              Hi Stefan,

                               

                              That's because this expression only calls out the possible ones whilst what we want is call out all those that match sum(distinctPartner)=0. Any idea how to do that?

                               

                              Also, can you show me how you fixed those other two expressions so I can see what I get?

                               

                              Thanks,

                              Linda

                                • Re: Dynamic listbox
                                  Stefan Wühl

                                  Well, I just posted the corrected version of one of the two suggested, haven't I?

                                   

                                  That's because this expression only calls out the possible ones whilst what we want is call out all those that match sum(distinctPartner)=0. Any idea how to do that?

                                   

                                  As I've understood, you want the same list of names as in the Red - Correct titled list box, right?

                                  I do get this result.

                                   

                                  But if I select years 2010 and 2011 as suggested, I don't see any results in Red - Correct list box.

                                   

                                  See attached my modified version with both versions of my suggested list box.

                                   

                                  Side note: I have no idea what your requirements are, but have you considered to use alternate states instead of the data island (available since QV11)?

                                   

                                  Regards,

                                  Stefan

                                    • Re: Dynamic listbox
                                      Linda Diodato

                                      Hi Stefan,

                                       

                                      Not exactly.

                                       

                                      Basically I want all the C_Clients whose names match the names of the Clients that are not in Green and Amber - those will automatically have 0 Partners. Maybe looking at it this other way round makes it clearer? Sorry I'm not explaining myself well enough.

                                       

                                      I'm on v10 so can't use alternate states.

                                       

                                      Thanks again,

                                      Linda

                                        • Re: Dynamic listbox
                                          Stefan Wühl

                                          Try

                                           

                                          =aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client)+e(Client) >} C_Client), C_Client)

                                            • Re: Dynamic listbox
                                              Linda Diodato

                                              I love you!

                                              • Re: Dynamic listbox
                                                Linda Diodato

                                                Hi Stefan,

                                                 

                                                I've realised I need to add another element to the expression. The e(Client) piece need to be related just to the possible Campaigns.

                                                 

                                                I've tried the following but neither seem to work - any idea?

                                                 

                                                =aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client)+e({<Campaign=p(Campaign)}>}Client) >} C_Client), C_Client)

                                                 

                                                 

                                                =aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client)+e({<Campaign = {$(=GetFieldSelections (Campaign))}>}Client) >} C_Client), C_Client)

                                                 

                                                Thanks,

                                                Linda

                                                  • Re: Dynamic listbox
                                                    Stefan Wühl

                                                    I can't see Campaign field in your sample file. Would be good if you could update your sample.

                                                    And it would also be helpful if you could explicitely state what your expect to see when selecting in fields (some sample scenarios).

                                                      • Re: Dynamic listbox
                                                        Linda Diodato

                                                        Hi,

                                                         

                                                        Sorry... sample file attached.

                                                         

                                                        Example:

                                                        Campaign 1 has a total of 6 Clients so if I select it our C_Client = Client with 0 Partners attempt listbox should only show the Clients with 0 Partners included in that Campaign, that is to say the ones that you can see in red. If we select Year 2012 on top of that, then C_Client = Client with 0 Partners should show the list below - that is to say the Clients with 0 Partners that are included in Campaign 1.

                                                         

                                                        BJNIT PWG / Kfqsrxr

                                                        JAI JBAM GCPDYNE XGQATLHH (PGHDESS) KYOTQZG / Ihotgke

                                                        MHGLZ RÜFGX BRYO & WY FQ / Esusrrf

                                                         

                                                        Finally, if nothing is selected in the Campaign field, the list should work as it already does.

                                                         

                                                        To sum up, when one or more Campaigns are selected, the scope of the listbox should be reduced to those Campaign only and further selections should work on top of that. With no Campaigns, the list should work on the entire pool of Clients, which exactly what it does with the latest calculate dimension you suggested.

                                                         

                                                        I've been trying this on my end, which works as long as I only make selections in the Campaign field or everywhere else. However, if I cobine selections in any field AND Campaign it shows nothing.

                                                         

                                                        =if(GetSelectedCount(Campaign)>=1,

                                                        aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client) >} C_Client), C_Client),

                                                        if(GetSelectedCount(Campaign)=0,

                                                        aggr( only({<C_Client = p({<Client = {"=sum(distinctPartner)=0"}>} Client)+e(Client) >} C_Client), C_Client)))

                                                         

                                                        Not sure what else I could test...

                                                         

                                                        Thanks,

                                                        Linda