Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

MonthStart function help

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.

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

6 Replies
maxgro
MVP
MVP

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

QlikView Date fields

Primer for QlikView Date fields

or post some rows of your excel

MarcoWedel

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

mahesh_agrawal
Creator
Creator

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

MarcoWedel

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

Not applicable
Author

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!

Not applicable
Author

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