topic Re: Max Week for Max Year in QlikView

Max Week for Max Year

Fri, 07 Dec 2012 09:06:49 GMT

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.

Re: Max Week for Max Year

Gysbert_Wassenaar — Fri, 07 Dec 2012 09:10:29 GMT

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

Re: Max Week for Max Year

Fri, 07 Dec 2012 09:18:04 GMT

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

Re: Max Week for Max Year

Fri, 07 Dec 2012 09:54:00 GMT

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 )

Re: Max Week for Max Year

Fri, 07 Dec 2012 10:28:02 GMT

Hi,

Try this..

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

Regards,

Chandra

Re: Max Week for Max Year

Gysbert_Wassenaar — Fri, 07 Dec 2012 10:57:11 GMT

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)

Re: Max Week for Max Year

Mon, 10 Dec 2012 13:16:22 GMT

Thanks chaps. Sorted.