Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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')))
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.
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
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.
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
the Month original format is in 'DD/MM/YYYY HH:MM:SS'
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.
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.
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