topic Re: How to restrict values in list box? in QlikView

How to restrict values in list box?

durgabhavani — Mon, 27 Apr 2015 16:40:19 GMT

Hi All,

I have dates for ten years in my calender object. (I am using date field from database in calender object)

But i don't want to display all 10 years values in calender. I need to display just latest year (2015) values in my calender.

(date format in my calender: mm/dd/yyyy.)

Please advice!

Thanks,

Durga

Re: How to restrict values in list box?

sunny_talwar — Mon, 27 Apr 2015 16:43:13 GMT

Use an expression instead of list box field: Only({<Date = {"=Year(Date) = 2015"}>} Date)

Best,

Sunny

Re: How to restrict values in list box?

durgabhavani — Mon, 27 Apr 2015 16:45:10 GMT

Thanks for response Sunny!

If i want to restrict values in calender then what to do?

Durga

Re: How to restrict values in list box?

sunny_talwar — Mon, 27 Apr 2015 16:47:17 GMT

Do you mean in the script itself? or are you talking about a calendar object?

Re: How to restrict values in list box?

petter — Mon, 27 Apr 2015 16:49:58 GMT

Insted of selecting a Field in the Field drop-down select <Expression> and then use the following expression:

=If( Year ( Date ) = 2015 , Date )

See below:

Re: How to restrict values in list box?

sunny_talwar — Mon, 27 Apr 2015 16:50:57 GMT

Petter do you know why the Only function isn't working here??? Is there a way to solve this with a set analysis??

Best,

Sunny

Re: How to restrict values in list box?

durgabhavani — Mon, 27 Apr 2015 16:53:01 GMT

Anyway sunny,

I want to restrict my dates for this year only. Either in script or directly in calender object!

Thanks,

Durga

Re: How to restrict values in list box?

sunny_talwar — Mon, 27 Apr 2015 16:55:23 GMT

In the script you can use a where statement like this:

LOAD yourFields,

Date

FROM xyz

Where Year(Date) = 2015;

On the front end you can use Petter Skjolden‌ for both the list box as well as calendar object. They both give you the ability to enter expression instead of using a field name directly.

Use this expression: =If( Year ( Date ) = 2015 , Date ) give by Petter.

HTH

Best,

Sunny

Re: How to restrict values in list box?

kashporov — Mon, 27 Apr 2015 16:56:29 GMT

Use this expression in List Box:

=aggr(distinct Only({<DateField={">$(=MakeDate(2015))"}>}DateField),DateField)

Re: How to restrict values in list box?

durgabhavani — Mon, 27 Apr 2015 17:01:20 GMT

Super sunny!

i thought only for listbox has the facility of expression. but i found expression in calender object and it works as well.

Thanks Petter!

Durga

Re: How to restrict values in list box?

petter — Mon, 27 Apr 2015 17:07:02 GMT

In this expression:

Only({<Date = {"=Year(Date) = 2015"}>} Date)

the set expression will return multiple dates if there are more than one row returned for any given date - and then Only() will not accept this but return NULL.