11 Replies Latest reply: Nov 22, 2011 8:38 AM by B Y

# Expression SUM with concatenation

hello,

I have a problem with my expression.

I try to do a sum, with a concatenation of 2 fields.

[civile_Year/month] = 01.2011, 02.2011, 03.2011, 04.2011 ....

[Num_month] = 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12

[civile_year] = 2008, 2009, 2010, 2011

I want to display the volume for the max date that the user have selected.

for example : if he select [civile_year] = 2011 and [Num_month] from 01 to 10, I want to display the volume for [civile_Year/month] = 10.2011

My expression :

=sum( {<[civile_Year/month]= {\$(=MaxString([Num_month]))&'.'&[civile_year]}>} Volume)

I have try lots of synthaxe like :

=sum( {<[civile_Year/month]= {(concat(\$(=maxstring([Num_month]))&\$(=max([civile_year])),'.')}> } Volume)

Someone can help me ?

• ###### Expression SUM with concatenation

Hi,

Why don't you make a new field in your script, concatenating them already:

[Num_month]&'.'&[civile_year] as MonthYear

=sum( {<[civile_Year/month]= {\$(=MaxString(MonthYear)}>} Volume)

I think it is a bit faster also.

Succes!

• ###### Expression SUM with concatenation

pls try this.....

in script part:

Year([civile_Year/month]) &''& num(Month([civile_Year/month]),00) as YearMonth

in expression

=sum({YearMonth={\$(=max(YearMonth))}}Volume)

Hope its works...

Regards,

Antony.

• ###### Expression SUM with concatenation

Yes, thanks, it's a good solution for the period acual.

but how can I get the volume for the previous period ?

because, if the user select the year 2011, I display a column with the volume 2011 and another with the volume of  previous period 2010.

and this expression don't work :

=sum({YearMonth={\$(=max(YearMonth)-1)}}Volume)

because YearMonth = 10.2011!

How can I compute 10.2010 in my expression ?? If I split YearMonth to make a substract it works, but I'm one more time on my initial problem : concatenation in my sum expression

• ###### Expression SUM with concatenation

Hi,

You can do this by making a extra field:

autonumber(YearMonth) as YearMonthID

Now it is easy to work with this ID.

Succes!

• ###### Expression SUM with concatenation

Do u want to show previous year last month ???

• ###### Expression SUM with concatenation

Pls try this.....

in script :

left(([civile_Year/month]),4) & right(([civile_Year/month]),2) as YearMonth

• ###### Expression SUM with concatenation

yes

• ###### Expression SUM with concatenation

previous year last month should be shown as default or should shown when u select a year ??

• ###### Re: Expression SUM with concatenation

Previous year last month must be display when a year and a month are selected.

example : if january 2011 is selected, the data displayed are 1 column with january 2011 and one with january 2010

• ###### Expression SUM with concatenation

In script :

left(([civile_Year/month]),4)  as Year,

left(([civile_Year/month]),4) & right(([civile_Year/month]),2) as YearMonth

from ......

jan 2011 :

=sum({Year ={\$(=max(Year))},YearMonth={\$(=max(YearMonth))}}Volume)

jan 2010 :

=sum({Year ={\$(=max(Year)-1)},YearMonth={\$(=max(YearMonth))}}Volume)

Regards,

Antony.

• ###### Re: Expression SUM with concatenation

it works, thanks you

• ###### Expression SUM with concatenation

and when it is january....201201 - 1......= 201200....

That's why working with an ID is better en easier.

Halmar