topic Re: issues with month() for dates crossing into a new month in App Development

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

Keitaru — Tue, 01 Oct 2019 05:43:43 GMT

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.

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

asinha1991 — Tue, 01 Oct 2019 06:03:12 GMT

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

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

Keitaru — Tue, 01 Oct 2019 07:32:23 GMT

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.

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

rathore01 — Tue, 01 Oct 2019 07:51:04 GMT

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

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

Keitaru — Tue, 01 Oct 2019 09:03:41 GMT

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.

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

tresB — Tue, 01 Oct 2019 09:30:56 GMT

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

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

Keitaru — Tue, 01 Oct 2019 09:57:23 GMT

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

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

tresB — Tue, 01 Oct 2019 10:17:05 GMT

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.

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

Keitaru — Tue, 01 Oct 2019 10:28:17 GMT

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.

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

asinha1991 — Tue, 01 Oct 2019 10:37:24 GMT

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

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

asinha1991 — Tue, 01 Oct 2019 10:41:54 GMT

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