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

Time Compare Problem (SQL)

Hi Everyone,

I'm getting crazy because - I guess - a small problem:

I import Data from a SQL-Database with OLE-DB - there is a data field (Date) with the format DD.MM.YYYY hh:mm.

My settings look like that:

SET TimeFormat='hh:mm';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm';


In another field a got some Entries per Month (SU_TIME) over 3 years.

Now I just want to show the average per month with AVG() and select one year as following:

AVG ({$<Date={2015}>}SU_TIME)

Dimenson is just Date.

The diagramm won't show any data to me.

Even simple if-statements do not work with Date ...


Hopefully you can help me!

Thanks a lot,

Jan

1 Solution

Accepted Solutions
henrikalmen
Specialist
Specialist

If you create a field named Year you could do like this:

     AVG ({$<Year={2015}>}SU_TIME)

But if you don't want to create a year field, you need to do something like this:

     AVG({$<Date={">=01.01.2015<=31.12.2015"}>}Date)

View solution in original post

13 Replies
henrikalmen
Specialist
Specialist

If you create a field named Year you could do like this:

     AVG ({$<Year={2015}>}SU_TIME)

But if you don't want to create a year field, you need to do something like this:

     AVG({$<Date={">=01.01.2015<=31.12.2015"}>}Date)

Anonymous
Not applicable
Author

a "dirty" solution could be perhaps:

AVG ({$<Date={"*2015*"}>}SU_TIME)

Anil_Babu_Samineni

This expression should work

AVG ({$<Date={2015}>}SU_TIME)


But, I doubt whether Date has 2015 Date. May be change it to Year rather Date

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hi Henrik,

I'll try tomorrow morning.

But why does it help to create another field? I already have mit "Date" Field with 2015, 2016, 2017 inside. You mean that Qlikview links  them afterwards automatically?

Thank you very much!

Not applicable
Author

Ah - I see what you mean!

Like a string-search

But why cannot I compare it like I do?

Would be nice to habe something like If(Date>2015,..,...)

Thank you!!

Not applicable
Author

Yes - I guess it should but it doesn't..

Date has for example: 01.05.2015 08:00

Thanks!

henrikalmen
Specialist
Specialist

Your date field contains day, month and year. If you have a field that contains only year, you can do as you tried. Otherwise you'll have to do a search in the field using set expression, in the way I suggested or with the actually simpler solution "*2015*" that Robin suggested.

henrikalmen
Specialist
Specialist

You can do something like:

     if(Year(Date)>2015, 'yes', 'no')

That would print "yes" if the year is larger than 2015, i.e. 2016 and onwards.

Not applicable
Author

Ahh - thank you very much!

I understood