Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jacobluft
New Contributor III

Max(Num(Month([Date]))) is <NULL>?

I'm wondering why when I try and make 2 variables

Let vMaxMonth = Max(Num(Month([Date])));

Let vPreviousMonth = If(Max(Num(Month([Date])))-1=0,12,Max(Num(Month([Date])))-1)

They both return Null Values.

When I checked the value of Max(Num(Month([Date]))) using a Temp table I saw it was returning a null value.

I was wondering if somebody can explain this to me?

I'm assuming Month([Date]) is returning a Null but I have no idea why and how to remedy the issue so it returns the month number instead.

File Names omitted my script is

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='M/DD/YY';

SET TimestampFormat='M/DD/YY hh:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-CA';

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

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Report:

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is US);

concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is CANADA);

concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

Concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is EMEA);

Concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is CANADA);

Concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is CanadaApril);

Concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is EMEAApril);

Concatenate

LOAD *,month(Date) as Month FROM [FILE]

(ooxml, embedded labels, table is USA);

    Load

    num(Month([Date])) as MaxMonth,

    IF(Max(num(month([Date])))-1=0,12,Max(num(month([Date])))-1) as PreviousMonth

    RESIDENT Report;

    Let vMaxMonth = MaxMonth;

    Let vPreviousMonth = PreviousMonth;

   

    drop table Temp

In regards to the concatenation it is due to later months having additional columns compared to the earlier months.

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

use the Date#() to specify the date format

TAB_MaxMonth:

    Load

    num(Month(Max(DATE#([Date],'DATEFORMAT')))) as MaxMonth,

    IF(num(Month(Max(DATE#([Date],'DATEFORMAT'))))-1=0,12,num(Month(Max(DATE#([Date],'DATEFORMAT'))))-1) as PreviousMonth

    RESIDENT Report;

    Let vMaxMonth = peek('TAB_MaxMonth',0,'MaxMonth')

    Let vPreviousMonth = peek('TAB_MaxMonth',0,'PreviousMonth');

11 Replies
vinieme12
Esteemed Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

try as below

TAB_MaxMonth:

    Load

    num(Month(Max([Date]))) as MaxMonth,

    IF(num(Month(Max([Date])))-1=0,12,num(Month(Max([Date])))-1) as PreviousMonth

    RESIDENT Report;

    Let vMaxMonth = peek('TAB_MaxMonth',0,'MaxMonth')

    Let vPreviousMonth = peek('TAB_MaxMonth',0,'PreviousMonth');

Frank_Hartmann
Honored Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

try like that:

Data:

load * Inline [

  Month,Sales

  01/01/2017,100

  01/02/2017,200

  01/03/2017,150

  01/04/2017,200

  01/05/2017,320

  01/06/2017,400

];

MaxMonth:

Load

  Max(Month) as  MaxMonth

Resident Data;

Let vMaxMonth = peek('MaxMonth',0,'MaxMonth');

Let vPreviousMonth = AddMonths(vMaxMonth,-1);

then create a textbox and insert:

='Max Month: ' & date(vMaxMonth)

and create another textbox and insert:

='Previous Month: ' & vPreviousMonth

hope this helps

vinieme12
Esteemed Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

are you sure about this?

Let vPreviousMonth = AddMonths(vMaxMonth,-1);

jacobluft
New Contributor III

Re: Max(Num(Month([Date]))) is <NULL>?

Hey,

I got an expression error when I tried this response.

Additionally will the AddMonths() work when the date format is in MM/DD/YYYY?

Thanks for your time!

Frank_Hartmann
Honored Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

why not? correct me if im wrong 🙂

jacobluft
New Contributor III

Re: Max(Num(Month([Date]))) is <NULL>?

Hello,

Both vMaxMonth and vPreviousmonth are returning null values.

I think my system might be interpreting [Date] as a String.

vinieme12
Esteemed Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

use the Date#() to specify the date format

TAB_MaxMonth:

    Load

    num(Month(Max(DATE#([Date],'DATEFORMAT')))) as MaxMonth,

    IF(num(Month(Max(DATE#([Date],'DATEFORMAT'))))-1=0,12,num(Month(Max(DATE#([Date],'DATEFORMAT'))))-1) as PreviousMonth

    RESIDENT Report;

    Let vMaxMonth = peek('TAB_MaxMonth',0,'MaxMonth')

    Let vPreviousMonth = peek('TAB_MaxMonth',0,'PreviousMonth');

vinieme12
Esteemed Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

The thing is the OP is trying to retrieve month numbers while you've directly used dates as Month and use Addmonth() with the date value...if the OP tries the same with a Numeric(month number) value that would not return correct results

Frank_Hartmann
Honored Contributor II

Re: Max(Num(Month([Date]))) is <NULL>?

Ahhh, you are absolutly right.

I was not reading in all detail.

thx for the hint