Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My script loads the data from an excel file and one field contains date values. I need to convert this date in to the 'MMM-YYYY' format.
The line of code is:
Date(MonthStart([Treatment Start Date], 'MMM-YYYY'))as TreatmentDate
The problem is that the dates do not appear in the list box after the script is executed.
Any help is greatly appreciated.
Kind regards,
Gayan.
Hi All,
First of all thank you for the input given with regard to my question. Your responses gave me an insight into experimenting with the date related functions. The functions did get me somewhere but it was not getting the desired results.
However I came across a line of code which was strangely close to the one I posted which was
Date(MonthStart([Treatment Start Date], 'MMM-YYYY')) as TreatmentDate
The code I found online was
Date(MonthStart([Treatment Start Date]), 'MMM YYYY') as TreatmentDate
Amazingly this gave the correct result set I needed.
It was the incorrect closure of brackets that caused the problem. In the original code the MonthStart gets the date input and the format where the output is passed on to the Date function. The revised code gets the date input and the formatting part is done by the Date function.
I'm no pro in this area but the help given by you guys and the bit of R&D helped to achieve the needed result.
Once again thank you all!
may be you need to convert excel field (according to excel format, ?????) using Date# and then Date(Date#...........)
Date#([Treatment Start Date], '????')
Here some detail
Primer for QlikView Date fields
or post some rows of your excel
for numerical date fields in excel:
Date(MonthStart([Treatment Start Date]), 'MMM-YYYY') as TreatmentDate
for date fields formated as text in excel:
Date(MonthStart(date#([Treatment Start Date],'MM/DD/YYYY')), 'MMM-YYYY') as TreatmentDate
adjust the first format string to your excel source
hope this helps
regards
Marco
Hi,
If u need your output in "MMM-YYYY" format, then why do u need to use MonthStart()?
First check whether your raw data is in default Date format or not,if not,then u have to use Date#() before using Date().
Date(Date#([Treatment start date],'format of rawa data'),'MMM-YYYY')
If it is in default date format
Use date() directlly.
Date([Treatment Start Date],'MMM-YYYY')
that you don't get results in your listbox suggests, that the excel values are not recognized as dates.
Check your date format in the script:
Adjust the line
SET DateFormat='M/D/YYYY';
to your excel date format.
the monthstart function in your script will not change the textual representation of your dates, but it will set the numerical representation to the first millisecond in this month, i,e, all dates in this month will get the exact same value for TreatmentDate, so TreatmentDates can be used as a common dimension for monthly aggregation.
hope this helps
regards
Marco
Hi All,
First of all thank you for the input given with regard to my question. Your responses gave me an insight into experimenting with the date related functions. The functions did get me somewhere but it was not getting the desired results.
However I came across a line of code which was strangely close to the one I posted which was
Date(MonthStart([Treatment Start Date], 'MMM-YYYY')) as TreatmentDate
The code I found online was
Date(MonthStart([Treatment Start Date]), 'MMM YYYY') as TreatmentDate
Amazingly this gave the correct result set I needed.
It was the incorrect closure of brackets that caused the problem. In the original code the MonthStart gets the date input and the format where the output is passed on to the Date function. The revised code gets the date input and the formatting part is done by the Date function.
I'm no pro in this area but the help given by you guys and the bit of R&D helped to achieve the needed result.
Once again thank you all!
Hi
Thanks for your effort in R & D it helped me to....
thanks a lot!!!
the hyphen(-) in between 'MMM-YYYY' is just a separator so it can also be included in the code if required.
Date(MonthStart([Treatment Start Date]), 'MMM-YYYY') as TreatmentDate