Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Thanks in advance.
Hi,
Why don't you make a new field in your script, concatenating them already:
[Num_month]&'.'&[civile_year] as MonthYear
Then use in your expression:
=sum( {<[civile_Year/month]= {$(=MaxString(MonthYear)}>} Volume)
I think it is a bit faster also.
Succes!
pls try this.....
in script part:
load
Year([civile_Year/month]) &''& num(Month([civile_Year/month]),00) as YearMonth
in expression
=sum({YearMonth={$(=max(YearMonth))}}Volume)
Hope its works...
Regards,
Antony.
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
Hi,
You can do this by making a extra field:
autonumber(YearMonth) as YearMonthID
Now it is easy to work with this ID.
Succes!
Do u want to show previous year last month ???
Pls try this.....
in script :
load
left(([civile_Year/month]),4) & right(([civile_Year/month]),2) as YearMonth
yes
previous year last month should be shown as default or should shown when u select a year ??
and when it is january....201201 - 1......= 201200....
That's why working with an ID is better en easier.
Halmar