excel problem

[beck_bakytbek]

Hi Folks,

i got a problem:

i have in excel data field: 01.02.2015 , if i import table in qlik sense, i am not able to implement this field in data field:

Year(Date#(Field,'DD-MM-YYYY')) it does not work

Does anybody have any idea?

Beck

[jaumecf23 · Accepted solution]

Maybe this : Year(Date#(Field,'DD.MM.YYYY'))

[Ivan_Bozov]

How about

Date(Date#(Field, 'DD.MM.YYYY'), 'YYYY')

[jonathandienst]

@Ivan: not a great idea. This will create 365 distinct values in  a year that all look exactly the same. Date() does not change the underlying value, it simply formats the display. So all values of Field in a year will have the same display text, but have distinct underlying values.

James' idea is correct

=Year(Date#(Field,'DD.MM.YYYY'))

[YoussefBelloum]

Hi,

because you have field with format: DD.MM.YYYY and you ask Qlik to evaluate it as a field with format: DD-MM-YYYY

it will not work.

you should evaluate it the way it is coming, like this:

Date#(Field,'DD.MM.YYYY')

after that you can format it the way you want, like this:

Date(Date#(Field,'DD.MM.YYYY'),'DD-MM-YYYY')

and if you need to extract some information like Year or month or wathever, you don't even need to format it, just evaluate it correctly

[beck_bakytbek]

Thanks a lor for your help and your time Ivan

[beck_bakytbek]

Thanks a lor for your help and your time Jaume

[beck_bakytbek]

Hi Folks,

i have a question : how can i implement it on Months and Quartals,

if implement it ,so i have only these

Month(Date#(Buchungsdatum,'DD.MM.YYYY')) as Month,

'Q'&ceil(Month(Date#(Buchungsdatum,'DD.MM.YYYY'))) as Quartal,

but it does not work

does anybody have any idea?

Beck

[big_dreams]

quarter should be like

'Q'&ceil(Month(Date#(Buchungsdatum,'DD.MM.YYYY'))/3) as Quartal,

If above expression not work for you then open new thread and provide what are you trying to achieve and what difficulties are you facing?? please explain little bit more.

Regards,