topic Re: help in list box expression in QlikView

help in list box expression

Anonymous — Sun, 19 Apr 2015 08:59:45 GMT

i have list box containing Sales_date .It has date from 2011 to 2015.

I need the only years of the sales_date like year(Sales_date).I need only distinct values like 2011,2012,2013,2014,2015.

Thanks

Re: help in list box expression

fred_s — Sun, 19 Apr 2015 09:27:03 GMT

Hi,

Create a listbox

Field <Expression>

=year(YourDate)

So you found the answer yourself.

Grtz Fred

Re: help in list box expression

MK_QSL — Sun, 19 Apr 2015 09:39:39 GMT

While Loading data in script just below Sales_date create one more field

Year(Sales_date) as Sales_Year

now you can use Sales_Year field in List Box..

Re: help in list box expression

fred_s — Sun, 19 Apr 2015 09:48:09 GMT

Yep, Manish's answer is the way to go, but since rand knew the year() function, I've assumed it was not what he was looking for.

Re: help in list box expression

Anonymous — Sun, 19 Apr 2015 11:26:32 GMT

Hi Fred,

Thanks for reply ! But i need only distinct year values. I am getting multiple year values.

Thanks

Re: help in list box expression

Anonymous — Sun, 19 Apr 2015 11:27:39 GMT

Thanks for the reply !!

But i need only distinct year values. I am getting multiple year values.

Thanks

Re: help in list box expression

fred_s — Sun, 19 Apr 2015 13:29:42 GMT

Show us an example of what you would like to achieve.

This is what I get and to me it's pretty distinct:

Re: help in list box expression

Anonymous — Mon, 20 Apr 2015 04:48:24 GMT

Hi ,

I need the same as above .

Thanks

Re: help in list box expression

PrashantSangle — Mon, 20 Apr 2015 04:56:24 GMT

Hi,

What is your date format??

Regards,

Re: help in list box expression

nagarjuna_kotha — Mon, 20 Apr 2015 05:02:56 GMT

Hi,

Can U share ur sample Data ?

Regards,

Nagarjuna

Re: help in list box expression

amit_saini — Mon, 20 Apr 2015 05:04:52 GMT

RGV,

Hope this will help u. Also if you are having different date format than do changes according .

Thanks,
AS

Re: help in list box expression

nagarjuna_kotha — Mon, 20 Apr 2015 05:06:42 GMT

Hi,

Try like this,

Year(FieldName) as Year

Example :

Ur fileld name is " Emp_Join_Date "

>>>>>Year( Emp_Join_Date) as Year.

You will get exactly as u want.

Regards,

Nagarjuna

Re: help in list box expression

jagan — Mon, 20 Apr 2015 05:25:24 GMT

Hi,

Try like this

Year(YearStart(Sales_date)) -- It will give distinct values.

The best solution is move this Year calculation to script, so that the performance would be better and logic is centralized.

LOAD

Year(Sales_date) AS Year

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: help in list box expression

Anonymous — Mon, 20 Apr 2015 07:48:23 GMT

may be your date format is the issue so try

Year(Date(Reported_Date)) as Year,

also try to convert in num() first then use date function if above is not working.

Thanks

BKC

Re: help in list box expression

Anonymous — Mon, 20 Apr 2015 09:18:06 GMT

Hi jagan,

I am still getting duplicate year values in list box

Here is my sample data

LOAD * INLINE [

Date

1/2/2011

2/2/2011

2/4/2014

4/10/2015

5/10/2015

21/10/2014

];

Thanks

Re: help in list box expression

jagan — Tue, 21 Apr 2015 02:39:36 GMT

Hi,

I am not getting the duplicates with below script, I am not sure.

LOAD * ,

Year(Date) AS Year

INLINE [

Date

1/2/2011

2/2/2011

2/4/2014

4/10/2015

5/10/2015

21/10/2014

];

But give a try with below script

LOAD * ,

Year(YearStart(Date)) AS Year

INLINE [

Date

1/2/2011

2/2/2011

2/4/2014

4/10/2015

5/10/2015

21/10/2014

];

Re: help in list box expression

Anonymous — Tue, 21 Apr 2015 11:38:38 GMT

Hi Jagan ,

Thanks for reply !

In script i am also not getting duplicate values .But i want to achieve it from list box expression.

Thanks

Re: help in list box expression

jagan — Tue, 21 Apr 2015 13:08:15 GMT

Use the same in listbox expression.