Time Compare Problem (SQL)

[#1]

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

[henrikalmen · Accepted solution]

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]

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

[#5]

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!

[#6]

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!!

[#7]

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

Date has for example: 01.05.2015 08:00

Thanks!

[henrikalmen]

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]

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.

[#10]

Ahh - thank you very much!

I understood