Max Week for Max Year

[#1]

Hello all. Expressions are mad!!!

This gives me the maximum year from my dataset irrespective of any selections (and its taken me long enough to get this)...

     Max( { 1 < MyYear= > } MyYear)

This gives me the maximum week from my dataset irrespective of any selections...

     Max( { 1 < MyWeek= > } MyWeek)

What I want to do is to combine these to give a sum of values for the max week of the max year. So how do I make something like this work?

=Sum( { 1 < Max( { 1 < MyYear= > } MyYear), Max( { 1 < MyWeek= > } MyWeek) >} total_value   )

Thanks.

Foldy.

[Gysbert_Wassenaar]

Try: Sum( { 1 < MyYear= {"$(=max(MyYear))"},MyWeek={"$(=max(MyWeek))"} > } total_value   )

[#3]

Hi,

Use like this

Sum( { 1 <MyYear= Max( MyYear ), MyWeek=Max( { 1 < MyWeek> } ) >} total_value   ).

The another way is create two variables

vMaxyear= Max( { 1 < MyYear= > } MyYear)

vMaxweek= Max( { 1 < MyWeek= > } MyWeek)

Sum( { 1 <MyYear= {$(vMaxyear)} ), MyWeek= {$( vMaxweek )}>} total_value   ).

Hope it helps..

Regards,

Chandra

[#4]

Thanks chaps for the quick replies. I've tried some of the techniques you both describe but without much success

Hello Gysbert, I've tried your idea  but unless I've typed it in wrongly, it seems that this is dependent on MyYear selections made elsewhere.

I need a static value of always max week for max year. I thought thats what the '1' at the start forced but I suspect the use of the =max(MyYear) is affected by the selections?

Chandra, I've also tried your idea of using variables but as when I tried something similar I'm getting zero returned.

When I use this with hardcoded values it works perfectly, so obviously I am not getting the syntax right with either variables or the set analysis...

=Sum( { 1 <MyYear= {2013} , MyWeek= {7}>} total_value  )

[#5]

Hi,

Try this..

Sum( { 1 <MyYear= {$(#vMaxyear)} ), MyWeek= {$(#vMaxweek )}>} total_value   ).

Regards,

Chandra

[Gysbert_Wassenaar · Accepted solution]

Yes, it takes the max year and week of your selection. If you don't want that you need to use to variables, e.g. v_MaxYear and v_MaxWeek.

The value of v_MaxYear is =max({1} MyYear)

The value of v_MaxWeek is =max({1<MyYear={$(v_MaxYear)}> MyWeek}

The sum expression will then be:

sum({1 <MyYear={$(v_MaxYear)},MyWeek={$(v_MaxWeek)} >} total_value)

[#7]

Thanks chaps. Sorted.