Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

issues with month() for dates crossing into a new month

Hi need some help with Month() or MonthName() when it comes to handling dates that are crossing over from an old month into a new Month.

 

I've  a list of dates say starts from 1 September 2019 12:00AM to 30 Septepmber 2019 23:59PM

here is my Data below : Data in both Weeks and Month Script are as followed:

 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-SG';
SET CreateSearchIndexOnReload=1;
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';

Load 

Closed Date,

month(Monthend(Date#([Closed Date],'DD\MM\YYYY hh:mm:ss'))) as Month,

num(Week(Date#([Closed Date],'DD/MM/YYY hh:mm:ss')),'00') as Week

From Sample.xlsx;

For some odd reason, the script is reading my 1st and 2nd Sep as AUG instead of SEP

First of the Week for me is Monday as shown in FirstWeekDay=0;

I've attached sample data below.

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

I see this is happening for border dates like 1st of Sep moves to Aug right?

it could be due to some conversion happening as I said due to timestamp  or some issue because of time.

so maybe it will work to consider this as text(or type cast using text function),remove time part and do calculation on month

View solution in original post

10 Replies
Highlighted
Creator III
Creator III

date# should use the same format as string, maybe try this?

month(monthend(Date#(subfield([closeddate],' ',1),'DD/MM/YYYY')))

if it doesn't work, check this and see if it returns right date

Date(Date#(subfield([closeddate],' ',1),'DD/MM/YYYY')))

 

Highlighted
Contributor III
Contributor III

I personally don't think there is anything wrong with my script just some how there is an odd bug in the back that assigning  week's previous month in to the new month.

Highlighted
Partner
Partner

Hi There, Please use below -

Month(monthend(Timestamp#([Closed Date],'DD/MM/YYYY hh.mm tt'), 'DD/MM/YYYY')) as Month

num(Week(Timestamp#([Closed Date],'DD/MM/YYY hh:mm:ss')),'00') as Week

No doubt on your script, it is just a small change!

Thanks, Rahul R

Highlighted
Contributor III
Contributor III

Hi Rahul

I've tried your tweak

Month(monthend(Timestamp#([Closed Date],'DD/MM/YYYY hh.mm tt'), 'DD/MM/YYYY')) as Month

Even through there was no much of a change in the displayed data, I did notice my chart didnt seem to read the set analysis well

LET vLastMonth = '*/'&date(AddMonths(date(Today(),'MM/YYYY'),-1,),'MM/YYYY');

vLastMonth = */09/2019

=count({<[Month]={'$(vLastMonth)'}>}[Problem ID])

so instead of showing me the month of September count which is 337 it showing something different.

However when i use */09/2019 in the smart search it works.

 

 

Highlighted
MVP
MVP

I couldn't reproduce the issue. However, since the excel data looks to have already timestamp values, I would suggest to use just month() and try like:

Month([Close Date]) as Month

Highlighted
Contributor III
Contributor III

the Month original format is in 'DD/MM/YYYY HH:MM:SS'

Highlighted
MVP
MVP

What is your data source? Try loading the timestamp field using month() as I said earlier. If that doesn't give you any output, that means qlik engine is not reading it as timestamp but string, in that case use month(date#(.. I don't see why you need monthend() here. Give a try.

Highlighted
Contributor III
Contributor III

I've attached here the original data set which I had pulled from my data source and did some data Masking

Previously I've not had any issues with this till recently which is why i'm quite puzzled to whats causing the problem. 

Highlighted
Creator III
Creator III

could it be because of this 

SET CollationLocale='en-SG'; in your system variable on top?

maybe because it is considering your date as UTC and converting it to SG?

I am just guessing, cant think of anything else..excel didn't show same issue in my env too