topic Re: MonthStart function help in QlikView

MonthStart function help

Tue, 05 Aug 2014 12:54:16 GMT

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.

Re: MonthStart function help

maxgro — Tue, 05 Aug 2014 13:02:16 GMT

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

Re: MonthStart function help

MarcoWedel — Tue, 05 Aug 2014 13:09:23 GMT

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

Re: MonthStart function help

mahesh_agrawal — Tue, 05 Aug 2014 13:10:44 GMT

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

Re: MonthStart function help

MarcoWedel — Tue, 05 Aug 2014 23:45:26 GMT

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

Re: MonthStart function help

Wed, 06 Aug 2014 08:14:53 GMT

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!

Re: MonthStart function help

Fri, 21 Nov 2014 13:26:24 GMT

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