Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

knightwriter
Contributor II

Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Nice easy one for some kind individual..... I have a date format dd/mm/yyyy in Excel and want the list box properties to show only the Year. How can this be done? First post on the community here so I'm hoping for a return! Many thanks, Stephen.

6 Replies

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Stephen

Try

     year(date#('DD/MM/YYYY')

Best Regards,     Bill

knightwriter
Contributor II

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Thanks for the speedy reply Bill.

Do I c&p this into the Script Editor by the Date Load?

Regards,

S

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Stephen

Yup.

Paste your script into this thread if you would like someone to look at it & suggest how ?

Best Regards,     Bill

Employee
Employee

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Hi,

If you only want the year you would use.

year(Fieldname) as year

Bill

Not applicable

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

I had this problem with an Excel report we got from "Hoopla". It looked like a date, but it wasn't a true Excel date, so none of the standard functions (as suggested above) worked for me. I tore my hair out trying to figure out what was wrong with my formulas, until I realised QV didn't know they were dates. Now I know, but I'm bald ;{

Here's what the 'date' looked like in the Excel (actually, a .CSV file):

7/29/2013 19:44

Here's what I put in my LOAD statement:

MakeDate(Mid([Join Date],index([Join Date],'/',2)+1,4),Left([Join Date],index([Join Date],'/',1)-1),Mid([Join Date],index([Join Date],'/',1)+1,index([Join Date],'/',2)-index([Join Date],'/',1)-1)) As NJDate,

Essentially, I went through the date, located the '/' delimiters, and used the "Index" function to find the positions dynamically (this adjusts automatically for single digit months or dates). Note that when you use Index like this, the 3rd parameter is the instance of the searched for text, so Index(text,'/',2) finds the SECOND instance of "/".

knightwriter
Contributor II

Re: Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

Thanks all, I got it it sorted.

@Kevin would it be rude to link rogaine?! Thanks for your help.

S

Community Browser