Dynamic listbox

[#1]

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!

[#2]

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

[#3]

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

[swuehl]

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)

[#5]

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

[swuehl]

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

[#7]

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

[swuehl]

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

[#9]

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

[swuehl · Accepted solution]

Try

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