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

[Anonymous]

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.

[vinieme12]

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]

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]

are you sure about this?

Let vPreviousMonth = AddMonths(vMaxMonth,-1);

[Anonymous]

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]

why not? correct me if im wrong 🙂

[Anonymous]

Hello,

Both vMaxMonth and vPreviousmonth are returning null values.

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

[vinieme12 · Accepted solution]

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]

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]

Ahhh, you are absolutly right.

I was not reading in all detail.

thx for the hint