topic Show only white (active) values in listbox in QlikView

Show only white (active) values in listbox

Fri, 04 May 2012 05:21:41 GMT

Hi All,

See, i have a list box showing all survey ids in my dashboad. Now when i do some selections, only those survey ids which are relevent to the selections will be seen in white and others will be in gray..yes, expected behaviour of qlikview.

Have a look at the below states of the mentioned list box..

Here , the figure 1 shows the default view of the list box when no selection is made.

Figure 2 shows the state of the listbox when some selections are made..see the scroll bar position, I have to scroll all the way towards right passing all grayed out surveys to find the relevent ones (in white) for the current selection.

Now my requirement is that, when I do some selections in other filters, i want to see only the active survey ids for that selection, in this list box. That means, I dont want to see gray color in this list box anytime.

There is an option in the list box properties which says 'Hide Excluded' ..i tried this, and when that option is enabled, the excluded survey ids are no longer shown but the vast area of gray option still exists making me to scroll all the way towards right to find the active survey ids. See in figure 3

Do anyone have any suggestion to help me achive my requirement?

Show only white (active) values in listbox

rwunderlich — Fri, 04 May 2012 05:26:30 GMT

Set the listbox sort to "Auto Ascending". This will cause your white entries to sort to the top of the listbox.

-Rob

Show only white (active) values in listbox

subin6600 — Fri, 04 May 2012 06:14:29 GMT

Hi, As Rob has mentioned you could sort the list box and it would make the available entries to be on the top.

Regards, Subin

Show only white (active) values in listbox

Fri, 04 May 2012 06:29:41 GMT

Hi Rob and Subin,

I tried this, but it is not working.

Even after checking the sort by state as auto ascending, the whicte entries are not shown at the begining

Show only white (active) values in listbox

martinpohl — Fri, 04 May 2012 06:45:48 GMT

in that case be sure that no other option is selected (numeric value or text)

Show only white (active) values in listbox

Fri, 04 May 2012 07:15:23 GMT

Hi,

can you try this?

go to your listbox properties

under "Fields", Select "Expression"

then type or copypaste

=aggr(only({<Surveys= P(Surveys)>}Surveys),Surveys)

under Title:

type "Surveys"

Hope this can help you^^

Best Regards,

Alex

Show only white (active) values in listbox

Fri, 04 May 2012 07:36:48 GMT

Alex,

The expression that I was using in list box (to filter the Survey IDs) till now was

=if([Survey Type]='Outside',SURVEY_ID)

Incorporating the expression you mentioned will result in something like this,

=aggr(only({<SURVEY_ID= P(SURVEY_ID)>}SURVEY_ID),SURVEY_ID)

In this, can you help me to include the [Survey Type] fiter condition also, so that I can try..

I tried but I am unsure about the syntax and it is failing.

Show only white (active) values in listbox

Fri, 04 May 2012 07:47:48 GMT

Alex,

Thanks a lot ..I think I got it.. can you please check the below expression and confirm if I got it right?

=aggr(only({<SURVEY_ID= P(SURVEY_ID),{[Survey Type]='Outside'}>}SURVEY_ID),SURVEY_ID)

Also, it would eb great help if you could explain this..like what the expression means..(am not that much into set analysis..and is eager to learn)..so that it would help me tackle set expression related challenges in future.

Show only white (active) values in listbox

Fri, 04 May 2012 08:05:48 GMT

Hi,

here^^

=aggr(only({<SURVEY_ID= P(SURVEY_ID),[Survey Type]= {'Outside'}>}SURVEY_ID),SURVEY_ID)

the curly bracket should e placed after your = sigh

{[Survey Type]= 'Outside'} should be [Survey Type]= {'Outside'}

glade to have helped you^^

Regards,

Alex

Show only white (active) values in listbox

Fri, 04 May 2012 08:19:37 GMT

to explain this,

{<SURVEY_ID= P(SURVEY_ID),[Survey Type]= {'Outside'}>}

P() function in P(SURVEY_ID) means Possible. We just Set SURVEY_ID (with the function Only()) to Possible value (which is your selection)

well set analysis is somewhat like if statement^^

=aggr(only({<SURVEY_ID= P(SURVEY_ID),[Survey Type]= {'Outside'}>}SURVEY_ID),SURVEY_ID)

so here we only set the values of SURVEY_ID to appear if SURVEY_ID = P(SURVEY_ID (or Possible or Selected value) AND if [Survey Type]= {'Outside'} (Survey type is outside)

we combined it with only function, well to make the values of SURVEY_ID ONLY if your condition is met

and Aggr() function. we used it here coz we will get an error with only function without aggr-ing it^^

I dont know if I explained it clearly, Sorry in advance if its hard to understand me lol

Regards,

Alex

Show only white (active) values in listbox

Fri, 04 May 2012 08:27:51 GMT

Alex ,

The explanation was clear enough. Thanks a tonne for your time and patience.

P (possible) function was a new information to me.

Much Regards,

Vishnu

Re: Show only white (active) values in listbox

Tue, 27 Jan 2015 10:28:12 GMT

Hi, This worked for me too!

Many thanks

Re: Show only white (active) values in listbox

berndjaegle — Thu, 11 Jun 2015 15:15:55 GMT

Thanks for this solution. Works fine for me, too. Would be nice to have this as "built in" operation in the QlikView cleint.

Re: Show only white (active) values in listbox

nathanwright — Wed, 23 Sep 2015 23:42:07 GMT

This works perfectly. Cheers!

Re: Show only white (active) values in listbox

Tue, 08 Mar 2016 17:37:49 GMT

Hi Alex,

Your logic gave me some hope. thanks a ton.

As per my requirement i need to do it in the same way but have to check "Always one selected value".

Any idea how to achieve this?? I am trying to write this expression in script editor but its not allowing me to do so.

Thanks

Walter

Re: Show only white (active) values in listbox

Wed, 09 Mar 2016 03:28:11 GMT

Hi Walter,

You need "Always one selected value" on the listbox you wrote this?

=aggr(only({<SAMPLE= P(SAMPLE)>}DATA),DATA) //let's call this listbox A

or to other listbox related to listbox A?

Regards,

Alex

Re: Show only white (active) values in listbox

Wed, 09 Mar 2016 14:55:05 GMT

Hi Alexis,

Thanks for the reply. But its giving me the same scenario like previous logic.

Can we write the expression in the Script Editor??

Thanks for your time.

Re: Show only white (active) values in listbox

Tue, 21 Jun 2016 14:24:43 GMT

Hi Alexis,

I tried your syntax with the P(), but still getting all the data.

I am trying to show the Months only related to my other selections. Could you please help me to rephrase the formula which you written...

=aggr(only({<Fiscal_Month=P(Fiscal_Month)>}Fiscal_Month), Fiscal_Month)

Could you please correct my above formula.

Kind Regards,

Balaji R