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

[Keitaru]

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.

[asinha1991]

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')))

[Keitaru]

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.

[rathore01]

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

[Keitaru]

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.

[tresesco]

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

[Keitaru]

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

[tresesco]

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.

[Keitaru]

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. 

[asinha1991]

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