Remove duplicates for the concatinated string in Textbox

[avinashkk696]

Hello All,

I have a year field with values 2001,2002,2003,2004,2005,2006,2007,2008,2009.  My requirement is, when I select multiple years, I want to display the selected years along with their previous years. For example if I select 2003,2004,2005 I want to display 2002,2003,2004,2005 in the textbox. I could not bring the output with the concat function. Please help me on this.

Thanks in advance

[arvind_patil]

HI Avinash,

You need to use concatenate function other wise concatenate operator like '&'.

Thanks,

Arvind Patil

[avinashkk696]

Hi Arvind,

I have used Concat(Year,',') which is giving me current selections. How can I get the previous years??

[rahulpawarb]

Hello Avinash,

May be this:

=Num(Left(Replace(GetCurrentSelections(), 'Year: ', ''), 4)) - 1 & ', ' & Replace(GetCurrentSelections(), 'Year: ', ' ')

Regards!

Rahul Pawar

[qv_testing]

Nice Rahul,

=IF(GetFieldSelections(Year)>0,

Num(Left(Replace(GetCurrentSelections(), 'Year: ', ''), 4)) - 1 & ', ' & Replace(GetCurrentSelections(), 'Year: ', ' '),

'Select Year')

[arulsettu]

may be like this

load *,

Previous(year) as previousyear;

Load * Inline [

year

2001

2002

2003

2004

2005

];

and expression in filter

previousyear&','& year

[effinty2112]

Hi Avinash,

Try this in a textbox:

=Concat({1}aggr(if(Not isnull(Below(Only(Year))),only({1}Year),Year),Year),',')

Cheers

Andrew

[stigchel]

You can remove the duplicates by using DISTINCT in the concat function. For also displaying previous years even though not selected, you can use set analysis that includes years smaller than the max year seklected:

=Concat({<year={"<=$(=Max(year))"}>} DISTINCT year,',')

Note that that you will have to replace 'year' with the exact name of your field year including capitals

Edit: <= instead of <