topic Removing one particular date from a list box using expression in QlikView

Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 15:56:44 GMT

Could anyone help me with this?

I have a list box with dates and i want to make a particular date null. In list box i want to make 1/1/11980 null but all other values stays as is.

Thanks,

Shan

Re: Removing one particular date from a list box using expression

datanibbler — Thu, 20 Nov 2014 15:59:51 GMT

Hi Shan,

I think listbox_elements are not particularly flexible in that respect. The best solution would probably be to just use a copy of that field that just has some text when there is this date in the original column.

Is that actually a date that is in your data? What is the reason you want to make that non_selectable (if there is a security issue, it might be a better alternative to use SECTION_ACCESS with a OMIT field?)

HTH

Best regards,

DataNibbler

Re: Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 16:07:33 GMT

DataNibbler,

Thanks for your response. Yes its a date in my data. I dont want to omit field, i just want to omit a field value or just want to make it null.

Thanks,

Shan

Re: Removing one particular date from a list box using expression

rubenmarin — Thu, 20 Nov 2014 16:08:01 GMT

Hi, you can use expression in listbox:

=Aggr(Only({1<[Install Date]={'>01/01/1990'}>} [Install Date]), [Install Date])

Re: Removing one particular date from a list box using expression

morgankejerhag — Thu, 20 Nov 2014 16:10:23 GMT

Just to clarify Rubens answer - when you create a list box you can scroll all the way to the bottom in the drop down where you select the field. There it says "expression"

Re: Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 16:11:34 GMT

Thanks Ruben!

That is doing the trick.

Shan

Re: Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 16:32:30 GMT

Is it possible to apply that in the text box?

Shan

Re: Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 16:36:08 GMT

I have one text box which is showing the Minimum install date and the expression for that is :

=Date(Min([Install Date]))

I was trying to apply your expression in this textbox expression. But its showing wrong expression.

Any help on that please!

Thanks,

Shan

Re: Removing one particular date from a list box using expression

rubenmarin — Thu, 20 Nov 2014 16:37:21 GMT

That expression returns all dates >01/01/1990. What exactly you want to put in text box?

If it's an expression filtering dates >01/01/1990 you can use something like:

Sum({<[Install Date]={'>01/01/1990'}>} Sales)

Sum({<[Install Date]*={'>01/01/1990'}>} Sales)

If you want to show all the dates in textbox you need to use a concatenate funcion:

Concatenate(Aggr(Only({1<[Install Date]={'>01/01/1990'}>} [Install Date]), [Install Date]))

Re: Removing one particular date from a list box using expression

rubenmarin — Thu, 20 Nov 2014 16:40:18 GMT

Ok, that should be:

=Date(Min({<[Install Date]={'>01/01/1990'}>} [Install Date])) --> Ignores selections in [Install Date]

=Date(Min({<[Install Date]*={'>01/01/1990'}>} [Install Date])) --> Selections in [Install Date] will count

Re: Removing one particular date from a list box using expression

AH — Thu, 20 Nov 2014 16:44:58 GMT

Thank you so much Ruben for your quick response!

Its working great as expected.

I started loving this community for folks like you guys!

Thanks,

Shan