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

topic Re: Max(Num(Month([Date]))) is <NULL>? in QlikView https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295005#M828896 <HTML><HEAD></HEAD><BODY><P>try as below</P><P></P><P>TAB_MaxMonth:</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Load</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    num(Month(Max([Date]))) as MaxMonth,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    IF(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max([Date])))</SPAN>-1=0,12,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max([Date])))</SPAN>-1) as PreviousMonth</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    RESIDENT Report;</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vMaxMonth = peek('<SPAN style="font-size: 13.3333px;">TAB_MaxMonth</SPAN><SPAN style="font-size: 13.3333px;">',</SPAN>0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MaxMonth'</SPAN>)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vPreviousMonth = <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">peek('<SPAN style="font-size: 13.3333px;">TAB_MaxMonth</SPAN></SPAN><SPAN style="font-size: 13.3333px;">',</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">PreviousMonth</SPAN></SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">'</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">);</SPAN></P></BODY></HTML> Wed, 28 Jun 2017 18:36:55 GMT vinieme12 2017-06-28T18:36:55Z Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295004#M828895 <HTML><HEAD></HEAD><BODY><P>I'm wondering why when I try and make 2 variables</P><P></P><P>Let vMaxMonth = Max(Num(Month([Date])));</P><P>Let vPreviousMonth = If(Max(Num(Month([Date])))-1=0,12,Max(Num(Month([Date])))-1)</P><P></P><P>They both return Null Values.</P><P>When I checked the value of Max(Num(Month([Date]))) using a Temp table I saw it was returning a null value.</P><P></P><P>I was wondering if somebody can explain this to me?</P><P></P><P>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.</P><P></P><P>File Names omitted my script is</P><P></P><P>SET ThousandSep=',';</P><P>SET DecimalSep='.';</P><P>SET MoneyThousandSep=',';</P><P>SET MoneyDecimalSep='.';</P><P>SET MoneyFormat='$#,##0.00;-$#,##0.00';</P><P>SET TimeFormat='hh:mm:ss TT';</P><P>SET DateFormat='M/DD/YY';</P><P>SET TimestampFormat='M/DD/YY hh:mm:ss[.fff] TT';</P><P>SET FirstWeekDay=6;</P><P>SET BrokenWeeks=1;</P><P>SET ReferenceDay=0;</P><P>SET FirstMonthOfYear=1;</P><P>SET CollationLocale='en-CA';</P><P>SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';</P><P>SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';</P><P>SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';</P><P>SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';</P><P></P><P>Report:</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is US);</P><P>concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is EMEA);</P><P>concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is CANADA);</P><P>concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is USA);</P><P>Concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is EMEA);</P><P>Concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is CANADA);</P><P>Concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is CanadaApril);</P><P>Concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is EMEAApril);</P><P>Concatenate</P><P>LOAD *,month(Date) as Month FROM [FILE]</P><P>(ooxml, embedded labels, table is USA);</P><P></P><P>    Load</P><P>    num(Month([Date])) as MaxMonth,</P><P>    IF(Max(num(month([Date])))-1=0,12,Max(num(month([Date])))-1) as PreviousMonth</P><P>    RESIDENT Report;</P><P></P><P>    Let vMaxMonth = MaxMonth;</P><P>    Let vPreviousMonth = PreviousMonth;</P><P>    </P><P>    drop table Temp</P><P></P><P>In regards to the concatenation it is due to later months having additional columns compared to the earlier months.</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295004#M828895 Anonymous 2020-11-25T16:16:04Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295005#M828896 <HTML><HEAD></HEAD><BODY><P>try as below</P><P></P><P>TAB_MaxMonth:</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Load</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    num(Month(Max([Date]))) as MaxMonth,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    IF(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max([Date])))</SPAN>-1=0,12,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max([Date])))</SPAN>-1) as PreviousMonth</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    RESIDENT Report;</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vMaxMonth = peek('<SPAN style="font-size: 13.3333px;">TAB_MaxMonth</SPAN><SPAN style="font-size: 13.3333px;">',</SPAN>0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">MaxMonth'</SPAN>)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vPreviousMonth = <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">peek('<SPAN style="font-size: 13.3333px;">TAB_MaxMonth</SPAN></SPAN><SPAN style="font-size: 13.3333px;">',</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">0,'<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">PreviousMonth</SPAN></SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">'</SPAN><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">);</SPAN></P></BODY></HTML> Wed, 28 Jun 2017 18:36:55 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295005#M828896 vinieme12 2017-06-28T18:36:55Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295006#M828897 <HTML><HEAD></HEAD><BODY><P>try like that:</P><P></P><P>Data:</P><P>load * Inline [</P><P>  Month,Sales</P><P>  01/01/2017,100</P><P>  01/02/2017,200</P><P>  01/03/2017,150</P><P>  01/04/2017,200</P><P>  01/05/2017,320</P><P>  01/06/2017,400</P><P>];</P><P></P><P>MaxMonth:</P><P>Load</P><P>  Max(Month) as  MaxMonth</P><P>Resident Data;</P><P></P><P>Let vMaxMonth = peek('MaxMonth',0,'MaxMonth');</P><P>Let vPreviousMonth = AddMonths(vMaxMonth,-1);</P><P></P><P></P><P>then create a textbox and insert: </P><P>='Max Month: ' & date(vMaxMonth)</P><P></P><P>and create another textbox and insert:</P><P>='Previous Month: ' & vPreviousMonth</P><P></P><P></P><P>hope this helps</P></BODY></HTML> Wed, 28 Jun 2017 18:49:49 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295006#M828897 Frank_Hartmann 2017-06-28T18:49:49Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295007#M828898 <HTML><HEAD></HEAD><BODY><P>are you sure about this?</P><P></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">Let vPreviousMonth = AddMonths(vMaxMonth,-1);</SPAN></P></BODY></HTML> Wed, 28 Jun 2017 19:14:30 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295007#M828898 vinieme12 2017-06-28T19:14:30Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295008#M828899 <HTML><HEAD></HEAD><BODY><P>Hey,</P><P>I got an expression error when I tried this response.</P><P>Additionally will the AddMonths() work when the date format is in MM/DD/YYYY?</P><P>Thanks for your time!</P></BODY></HTML> Wed, 28 Jun 2017 19:36:34 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295008#M828899 Anonymous 2017-06-28T19:36:34Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295009#M828900 <HTML><HEAD></HEAD><BODY><P>why not? correct me if im wrong <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P></BODY></HTML> Wed, 28 Jun 2017 19:45:46 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295009#M828900 Frank_Hartmann 2017-06-28T19:45:46Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295010#M828901 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P>Both vMaxMonth and vPreviousmonth are returning null values.</P><P>I think my system might be interpreting [Date] as a String.</P></BODY></HTML> Wed, 28 Jun 2017 20:00:35 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295010#M828901 Anonymous 2017-06-28T20:00:35Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295011#M828902 <HTML><HEAD></HEAD><BODY><P>use the Date#() to specify the date format </P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">TAB_MaxMonth:</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Load</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    num(Month(Max(DATE#([Date],'<STRONG>DATEFORMAT</STRONG>')))) as MaxMonth,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    IF(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max(DATE#([Date],'</SPAN><STRONG style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">DATEFORMAT</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">'))))</SPAN>-1=0,12,<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">num(Month(Max(DATE#([Date],'</SPAN><STRONG style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">DATEFORMAT</STRONG><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">'))))</SPAN>-1) as PreviousMonth</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    RESIDENT Report;</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vMaxMonth = peek('<SPAN style="font-weight: inherit; font-style: inherit; font-size: 13.3333px; font-family: inherit;">TAB_MaxMonth</SPAN><SPAN style="font-weight: inherit; font-style: inherit; font-size: 13.3333px; font-family: inherit;">',</SPAN>0,'<SPAN style="font-weight: inherit; font-style: inherit;">MaxMonth'</SPAN>)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">    Let vPreviousMonth = <SPAN style="font-weight: inherit; font-style: inherit;">peek('<SPAN style="font-weight: inherit; font-style: inherit; font-size: 13.3333px; font-family: inherit;">TAB_MaxMonth</SPAN></SPAN><SPAN style="font-weight: inherit; font-style: inherit; font-size: 13.3333px; font-family: inherit;">',</SPAN><SPAN style="font-weight: inherit; font-style: inherit;">0,'<SPAN style="font-weight: inherit; font-style: inherit;">PreviousMonth</SPAN></SPAN><SPAN style="font-weight: inherit; font-style: inherit;">'</SPAN><SPAN style="font-weight: inherit; font-style: inherit;">);</SPAN></P></BODY></HTML> Wed, 28 Jun 2017 20:14:58 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295011#M828902 vinieme12 2017-06-28T20:14:58Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295012#M828903 <HTML><HEAD></HEAD><BODY><P>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</P></BODY></HTML> Wed, 28 Jun 2017 20:20:41 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295012#M828903 vinieme12 2017-06-28T20:20:41Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295013#M828904 <HTML><HEAD></HEAD><BODY><P>Ahhh, you are absolutly right.</P><P>I was not reading in all detail.</P><P>thx for the hint</P></BODY></HTML> Wed, 28 Jun 2017 20:28:38 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295013#M828904 Frank_Hartmann 2017-06-28T20:28:38Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295014#M828905 <HTML><HEAD></HEAD><BODY><P>     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.</P><P></P><P>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.</P><P></P><P>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.</P><P></P><P>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.</P><P></P><P>The way I'm attempting to use the created variables is like this,</P><P></P><P>Sum({$<[Month] = {$(vMaxMonth)}>}[Sales]) - Sum({$<[Month] = {$(vPreviousMonth)}>}[Sales])</P><P></P><P>Thanks for all the help so far!</P></BODY></HTML> Wed, 28 Jun 2017 20:41:50 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295014#M828905 Anonymous 2017-06-28T20:41:50Z Re: Max(Num(Month([Date]))) is <NULL>? https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295015#M828906 <HTML><HEAD></HEAD><BODY><P>useful References</P><P></P><P><A href="https://community.qlik.com/qlik-blogpost/4394">Dates in Set Analysis</A></P><P></P><P><A href="https://community.qlik.com/docs/DOC-8843">How to use - Master-Calendar and Date-Values</A></P><P></P><P><A href="https://community.qlik.com/docs/DOC-16968">Period Presets: Compare Periods on the fly</A></P></BODY></HTML> Wed, 28 Jun 2017 20:48:38 GMT https://community.qlik.com/t5/QlikView/Max-Num-Month-Date-is-lt-NULL-gt/m-p/1295015#M828906 vinieme12 2017-06-28T20:48:38Z