Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck110979
Valued Contributor III

excel problem

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

1 Solution

Accepted Solutions
jaumecf23
Contributor III

Re: excel problem

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

8 Replies
jaumecf23
Contributor III

Re: excel problem

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

Ivan_Bozov
Valued Contributor

Re: excel problem

How about

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

MVP
MVP

Re: excel problem

@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
Esteemed Contributor

Re: excel problem

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


beck110979
Valued Contributor III

Re: excel problem

Thanks a lor for your help and your time Ivan

beck110979
Valued Contributor III

Re: excel problem

Thanks a lor for your help and your time Jaume

beck110979
Valued Contributor III

Re: excel problem

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
Contributor III

Re: excel problem

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,

Community Browser