Why does this SET stmt work for selecting a 3-char 'mmm' Month??

[ambetts2]

Hello all, I'm fairly new to the SET statement and I've been recently asked to make changes to an existing document. I came across the below code which selects the Maximum Year selected and the Maximum Month for the Maximum Year selected. The Year selection makes perfect sense to me. However. I do not understand why for the month the set is repeated and includes -1 at the end. I'm trying to learn and want to make some sense out of this syntax.

Also, does anyone know of another shorter syntax to acquire the Max Month here?

Month is in the format: Jan, Feb, Mar, ...

Thanks all in advance for any assistance here.

Andreas

Sum({$<Year = {$(=max(Year))},

            Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))>$(=max({<Year={$(=max(Year))}>} Month)-1)"}>} 

               [Sales Amount])

[whiteline]

Hi.

Month = {"=Month=$(=max(Month))"}

[ambetts2]

GM whiteline and thanks for the response but this doesn't work.

Max() is returning a numeric say 09 and Month contains and alpha say 'Sep', so the two are not being matched.

I also trine Num(Month) = {$(=max(Month))} but the statement did not like the function Num().

[tresesco]

Have you tried like?

Month={"$(=Month(Max(Month)))"}

[somenathroy]

You may try :

Sum({$<Year = {'$(=max(Year))'}, Month = {'$(=Max({<Year={"$(=Max(Year))"}>}Month))'}>} [Sales Amount])

Regards,

Som

[somenathroy]

Your existing exp. does say that the target month is less or equal to Max Month(Max Year) and greater than the Previous Month(Max Month(Max Year)) . It obviously does mean that target month is equal to Max Month(Max Year).

hope it would help you to decide over expr.

Regards,

Som

[jonathandienst]

Hi

If Month is text, like Jan, Feb, Mar..., then this should work:

Month = {"$(=Date(MakeDate(Max(Year), max({<Year = {"$(=Max(Year))"}>} Month)), 'MMM'))"}

Regards

Jonathan

[whiteline]

Max() is returning a numeric say 09 and Month contains and alpha say 'Sep', so the two are not being matched.

You don't even guess how it really works.

Max() can handle only numeric values, Max(Month) returns appropriate number because Month is dual (Both number and string).

QlikView can handle comparisons for duals like Month=9 but in set expression by default 'search' method is used instead.

[Carlos_Reyes]

For that requirement I usually use a MonthYear field that is generated with the MonthName function:

SUM(  { $ <  MonthYear={"$(=MonthName(Max(MonthYear)))"} > }    [Sales Amount] )