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.

[Anonymous]

     A bit of context as to what I'm trying to do. I have several Dates for reports in different months of 2017. However due to inconsistent uploading the day of the month they are uploaded varies. For the sake of my data being "Neat" I'm just trying to work with months to omit days entirely, as it is a monthly report anyways.

The problem I've run into on Qlikview compared to Power BI and Tableau is that it seems much harder to generate a differences Measure for data. That is to say, comparing a Sum of "Sales" from one month to another.

My first thought was to just index the months using their respective month number. Doing a bit of googling on this idea led me to other posts in this Forum in which people seemed to being doing this using the Max() and Max()-1 functions on time data. However, when I attempted to recreate that following their posted examples I ran into this issue of Null values occurring instead of the months.

Not sure if the in depth explanation can clear the water on what exactly I'm looking for. I just recently started working with Qlik due to its ability to maintain filtering while drilling through pages and am having a bit of trouble with the syntax of the Script and Expressions. Any ideas on how I'd achieve this result would be very useful.

The way I'm attempting to use the created variables is like this,

Sum({$<[Month] = {$(vMaxMonth)}>}[Sales]) - Sum({$<[Month] = {$(vPreviousMonth)}>}[Sales])

Thanks for all the help so far!

[vinieme12]

useful References

Dates in Set Analysis

How to use - Master-Calendar and Date-Values

Period Presets: Compare Periods on the fly