topic Re: Why does this SET stmt work for selecting a 3-char 'mmm' Month?? in QlikView

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

ambetts2 — Mon, 09 Sep 2013 18:36:46 GMT

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])

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

whiteline — Mon, 09 Sep 2013 20:13:56 GMT

Hi.

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

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

ambetts2 — Tue, 10 Sep 2013 12:28:33 GMT

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().

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

tresB — Tue, 10 Sep 2013 12:33:45 GMT

Have you tried like?

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

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

somenathroy — Tue, 10 Sep 2013 12:38:03 GMT

You may try :

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

Regards,

Som

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

somenathroy — Tue, 10 Sep 2013 12:47:31 GMT

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

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

jonathandienst — Tue, 10 Sep 2013 12:58:26 GMT

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

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

whiteline — Tue, 10 Sep 2013 19:52:28 GMT

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.

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

Carlos_Reyes — Tue, 10 Sep 2013 20:10:43 GMT

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

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