topic Re: Questin regarding Month() function in QlikView

Questin regarding Month() function

jduenyas — Tue, 09 Apr 2013 18:51:55 GMT

Hi all

in the Help section the description of the Month() function says as follows:

Month. Returns a text string representing the month when the fraction of expr is interpreted as a date, but can be formatted as a number.

Example:
month( '1971-10-30' ) returns Oct.

(Worth noting that the functions Year() and Day() return integer and not text)

However, in the Edit Script section, when building a calendar and using the Month() function it returns an integer 1, 2, 3, ...

Why is the difference and how can I get the numerical value of the Month(Today()) or Month(Now())?

Thanks

Re: Questin regarding Month() function

Tue, 09 Apr 2013 18:57:48 GMT

Try Num(Month(Today())).

Month is a dual function. It returns a String or a Number depends on the data type.

If you use it in a text object, it returns a string, but you can force it to be a number using Num() function.

Re: Questin regarding Month() function

Fri, 06 Sep 2013 11:43:55 GMT

I am using =Num(Month(Today()))

But in the output, I want to see 09 instead of 9 (for the month of September for example). Is there anyway to do that?

Thanks,

Sagar

Re: Questin regarding Month() function

Fri, 06 Sep 2013 22:45:59 GMT

include the format.

=num(month(today()), '00')

Sean

Re: Questin regarding Month() function

evan_kurowski — Tue, 15 Apr 2014 20:52:05 GMT

Months are actually stored as dual values in QlikView. (as may be other fields in your data model you might not expect).

Try the two expressions in listboxes

=Num(Month(Today()))

=Text(Month(Today()))

Also note that in the script you don't have a default table to assign the number to associate to a month string. However the 'default' scripting that comes with every application (english version)contains the line

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

watch what happens to the text output of the month functions when the names are redefined

SET MonthNames='Jorg;Furb;Mir;Ogre;May;Junk;Jules;Arrg;Sep;Ork;Nov;Dec';

Sometimes you will see inline tables in application, often to assign fiscal or other numbering systems to month names

LOAD

* INLINE [
    Month, Month_Num, Fiscal_Month
    Jan, 1, 4
    Feb, 2, 5
    Mar, 3, 6
    Apr, 4, 7
    May, 5, 8
    Jun, 6, 9
    Jul, 7, 10
    Aug, 8, 11
    Sep, 9, 12
    Oct, 10, 1
    Nov, 11, 2
    Dec, 12, 3
];

So by default, how is the expression you use going to interpret in terms of DUAL??? It's simple which ever way you needed the dual data type to evaluate in order for your expression to work, Month() will return the value that you DON'T want. (It's almost psychic in knowing this). If you were expecting text it will return a num and if you were hoping for the num it will return text. Force it to yield up it's usefulness by wrapping it in either Num() or Text() functions and denying it's ability to wriggle onto the other side!

Re: Questin regarding Month() function

Anonymous — Tue, 15 Apr 2014 20:53:29 GMT

The post on this thread from Evan Kurowski was accidentally deleted and had been marked as the correct answer. In order to restore points to all participants I have copied, reinserted and remarked as correct. Thanks for your patience!

Re: Questin regarding Month() function

marcomartinetti — Wed, 03 Jul 2019 08:17:52 GMT

hi all,

i want to create a function month(today()) in the set analysis, however that makes reference to months before. for now, i've written this formula in relation a month immediately antecedent:

Sum({<typ_misura_liv2={'FATT'}, mese_COM={$(=Month(Month(Today()-1)))}>}Sales)

the problem is when i try to go as behind on the months, because i put for example "-3" and data don't change.

can you help me understand ehat could be the problem?

Thanks