Need help in set expression

[#1]

Hi Friends,

A table that contains Year, Week, invoice number. As I would like to show the Last week value of Purchase_Order

I tried Maximum of Year and Max of Week -1 but it shows error.

YEAR	WEEK	Sum(Purchase_Order)
2012	21	15000
2012	22	24000
…..	…….	……….
2013	01	15000
2013	02	24000
2013	03	16000
……	….	….
2013	24	103560
2013	25	563200

could you please help me...

Regards

Chriss

[#2]

if(year=max(year) and month=max(month),sum(Purchase_Order))

[alexandros17]

Sum({$ <WEEK={'$(=Week(Now()))',year={'$(=year(Now())),'}>} Purchase_Order)

hope it helps

[#4]

Aggr(if( month=max(month),sum(Purchase_Order)),year)

[#5]

Hi Vishwa,

Thanks for yor reply, both condtions which are not working

=Num(Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK-1))"}>}Purchase_Order),'# ##0'), but it takes the total of all weeks, the aim is I would like to show the last week value only in a pivot table.

Hope I am clear to you.

Regards

Chriss

[prodanov]

Hi,

     This may be will help you.

Regards

[kumarnatarajan]

Hi,

1. First Add "Year" Dimention.

2. Second Add calculated dimention Below script

=aggr(max(Week),Year)

3. Then add Expression

=Sum(Purchase)

Find the attached file.

[prodanov]

Hi, results are not corrected!

[kumarnatarajan]

Hi,

Use this expression.

Ex: if(Aggr(NODISTINCT max(Week),Year)=max(Week) and Aggr(NODISTINCT max(Year),Year)=max(Year),Purchase)

Find the attached file.