Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jduenyas
Specialist
Specialist

Questin regarding Month() function

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

1 Solution

Accepted Solutions
evan_kurowski
Specialist
Specialist

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!

View solution in original post

6 Replies
Not applicable

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. 

Not applicable

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

Not applicable

include the format.

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

Sean

evan_kurowski
Specialist
Specialist

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!

View solution in original post

Anonymous
Not applicable

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!

marcomartinetti
Contributor II
Contributor II

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